Skip Headers

Oracle® Call Interface Programmer's Guide, 10g Release 2 (10.2) Part Number B14250-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 9 OCI Programming Advanced Topics

This chapter contains these topics:

- Overview of OCI Multithreaded Development

- The OCIThread Package

- Connection Pooling in OCI

- Session Pooling in OCI

- When to Use Connection Pooling, Session Pooling, or Neither

- Statement Caching in OCI

- User-Defined Callback Functions in OCI

- Transparent Application Failover Callbacks in OCI

- HA Event Notification

- OCI and Streams Advanced Queuing

- Publish-Subscribe Notification in OCI

- Database Change Notification

- Database Startup and Shutdown

## Overview of OCI Multithreaded Development

Threads are lightweight processes that exist within a larger process. Threads share the same code and data segments but have their own program counters, machine registers, and stacks. Global and static variables are common to all threads, and a mutual exclusivity mechanism is required to manage access to these variables from multiple threads within an application.

Once spawned, threads run asynchronously with respect to one another. They can access common data elements and make OCI calls in any order. Because of this shared access to data elements, a synchronized mechanism is required to maintain the integrity of data being accessed.

The mechanism to manage data access takes the form of mutexes (mutual exclusivity locks), that is implemented to ensure that no conflicts arise between multiple threads accessing shared internal data that are opaque to users. In OCI, mutexes are granted for each environment handle.

The thread safety feature of the Oracle Database server and OCI libraries allows developers to use the OCI in a multithreaded environment. Thread safety ensures code can be reentrant, with multiple threads making OCI calls without side effects.

Note: Thread safety is not available on every operating system. Check your Oracle system-specific documentation for more information. In a multithreaded UNIX environment, OCI calls are not allowed in a user signal handler, except for `OCIBreak()`.

### Advantages of OCI Thread Safety

The implementation of thread safety in OCI has the following advantages:

- Multiple threads of execution can make OCI calls with the same result as successive calls made by a single thread.

- When multiple threads make OCI calls, there are no side effects between threads.

- Users who do not write multithreaded programs do not pay a performance penalty for using thread-safe OCI calls.

- Use of multiple threads can improve program performance. Gains may be seen on multiprocessor systems where threads run concurrently on separate processors, and on single processor systems where overlap can occur between slower operations and faster operations.

### OCI Thread Safety and Three-Tier Architectures

In addition to client/server applications, where the client can be a multithreaded program, a typical use of multithreaded applications is in three-tier, or client-agent-server, architectures. In this architecture the client is concerned only with presentation services. The agent, or application server, processes the application logic for the client application. Typically, this relationship is a many-to-one relationship, with multiple clients sharing the same application server.

The server tier in this scenario is a database. The applications server, or agent, is very well suited to being a multithreaded application server, with each thread serving a single client application. In an Oracle environment this application server is an OCI or precompiler program.

### Implementing Thread Safety

In order to take advantage of thread safety, an application must be running on a thread-safe operating system. The application specifies that it is running in a multithreaded environment by making an `OCIEnvNlsCreate()` call with `OCI_THREADED` as the value of the `mode` parameter.

All subsequent calls to `OCIEnvNlsCreate()` must also be made with `OCI_THREADED`.

Note: Applications running on non-thread-safe operating systems must not pass a value of `OCI_THREADED` to `OCIInitialize()` or `OCIEnvNlsCreate()`.

If an application is single-threaded, whether or not the operating system is thread-safe, the application must pass a value of `OCI_DEFAULT` to `OCIInitialize()` or `OCIEnvNlsCreate()`. Single-threaded applications that run in `OCI_THREADED` mode may incur lower performance.

If a multithreaded application is running on a thread-safe operating system, the OCI library will manage mutexes for the application for each environment handle. An application can override this feature and maintain its own mutex scheme by specifying a value of `OCI_NO_MUTEX` in the `mode` parameter of the `OCIEnvCreate()` call.

The following scenarios are possible, depending on how many connections exist in each environment handle, and how many threads are spawned in each connection.

1. If an application has multiple environment handles, with a single thread in each, mutexes are not required.

2. If an application running in `OCI_THREADED` mode maintains one or more environment handles, with multiple connections, it has these options:

   - Pass a value of `OCI_NO_MUTEX` for the `mode` of `OCIEnvNlsCreate()`. The application must mutex OCI calls made on the same environment handle. This has the advantage that the mutex scheme can be optimized to the application design. The programmer must also insure that only one OCI call is in process on the environment handle connection at any given time.

   - Pass a value of `OCI_DEFAULT` for the `mode` of `OCIEnvNlsCreate()`. The OCI library automatically gets a mutex on every OCI call on the same environment handle.

     Note: The bulk of processing of an OCI call happens on the server, so if two threads using OCI calls go to the same connection, then one them can be blocked while the other finishes processing at the server. Use one error handle for each thread in an application, since OCI errors can be over-written by other threads.

#### Mixing 7.x and Later Release OCI Calls

If an application is mixing later release and 7.x OCI calls, and the application has been initialized as thread-safe (with the appropriate calls of the later release), it is not necessary to call `opinit()` to achieve thread safety. The application will get 7.x behavior on any subsequent 7.x function calls.

## The OCIThread Package

The `OCIThread` package provides a number of commonly used threading primitives. It offers a portable interface to threading capabilities native to various operating systems, but does not implement threading on operating systems that do not have native threading capability.

`OCIThread` does not provide a portable implementation, but it serves as a set of portable covers for native multithreaded facilities. Therefore, operating systems that do not have native support for multithreading will only be able to support a limited implementation of the `OCIThread` package. As a result, products that rely on all of `OCIThread`'s functionality will not port to all operating systems. Products that must port to all operating systems must use only a subset of `OCIThread`'s functionality.

The `OCIThread` API consists of three main parts. Each part is described briefly here. The following subsections describe each in greater detail.

- Initialization and Termination. These calls deal with the initialization and termination of `OCIThread` context, which is required for other `OCIThread` calls.

  `OCIThread` only requires that the process initialization function, `OCIThreadProcessInit()`, is called when `OCIThread` is being used in a multithreaded application. Failing to call `OCIThreadProcessInit()` in a single-threaded application is not an error.

  Separate calls to `OCIThreadInit()` will all return the same `OCIThread` context. Each call to `OCIThreadInit()` must eventually be matched by a call to `OCIThreadTerm()`.

- Passive Threading Primitives.Passive threading primitives are used to manipulate mutual exclusion locks (mutex), thread IDs, and thread-specific data keys. These primitives are described as passive because while their specifications allow for the existence of multiple threads, they do not require it. It is possible for these primitives to be implemented according to specification in both single-threaded and multithreaded environments. As a result, `OCIThread` clients that use only these primitives will not require a multiple-thread environment in order to work correctly. They will be able to work in single-threaded environments without branching code.

- Active Threading Primitives. Active threading primitives deal with the creation, termination, and manipulation of threads. These primitives are described as active because they can only be used in true multithreaded environments. Their specification explicitly requires multiple threads. If you need to determine at runtime whether or not you are in a multithreaded environment, call `OCIThreadIsMulti()` before using an `OCIThread` active primitive.

  In order to write a version of the same application to run on single-threaded operating system, it is necessary to branch your code, whether by branching versions of the source file or by branching at runtime with the `OCIThreadIsMulti()` call.

  See Also: "Thread Management Functions" `cdemothr.c` in the `demo` directory is an example of a multithreading application.

### Initialization and Termination

The types and functions described in this section are associated with the initialization and termination of the `OCIThread` package. `OCIThread` must be initialized before any of its functionality can be used.

The observed behavior of the initialization and termination functions is the same regardless of whether `OCIThread` is in single-threaded or multithreaded environment. Table 9-1 lists functions for thread initialization and termination.

Table 9-1 Initialization and Termination Multithreading Functions

Function | Purpose
--- | ---
`OCIThreadProcessInit()` | Performs OCIThread process initialization.
`OCIThreadInit()` | Initializes OCIThread context.
`OCIThreadTerm()` | Terminates the OCIThread layer and frees context memory.
`OCIThreadIsMulti()` | Tells the caller whether the application is running in a multithreaded environment or a single-threaded environment.

See Also: "Thread Management Functions"

#### OCIThread Context

Most calls to `OCIThread` functions use the OCI environment or user session handle as a parameter. The `OCIThread` context is part of the OCI environment or user session handle and it must be initialized by calling `OCIThreadInit()`. Termination of the `OCIThread` context occurs by calling `OCIThreadTerm()`.

Note: The `OCIThread` context is an opaque data structure. Do not attempt to examine the contents of the context.

### Passive Threading Primitives

The passive threading primitives deal with the manipulation of mutex, thread ID's, and thread-specific data. Since the specifications of these primitives do not require the existence of multiple threads, they can be used both in multithreaded and single-threaded operating systems. Table 9-2 lists functions used to implement passive threading.

Table 9-2 Passive Threading Primitives

Function | Purpose
--- | ---
`OCIThreadMutexInit()` | Allocates and initializes a mutex.
`OCIThreadMutexDestroy()` | Destroys and deallocates a mutex.
`OCIThreadMutexAcquire()` | Acquires a mutex for the thread in which it is called.
`OCIThreadMutexRelease()` | Releases a mutex.
`OCIThreadKeyInit()` | Allocates and initializes a key.
`OCIThreadKeyDestroy()` | Destroys and deallocates a key.
`OCIThreadKeyGet()` | Gets the calling thread's current value for a key.
`OCIThreadKeySet()` | Sets the calling thread's value for a key.
`OCIThreadIdInit()` | Allocates and initializes a thread ID.
`OCIThreadIdDestroy()` | Destroys and deallocates a thread ID.
`OCIThreadIdSet()` | Sets on thread ID to another.
`OCIThreadIdSetNull()` | Nulls a thread ID.
`OCIThreadIdGet()` | Retrieves a thread ID for the thread in which it is called.
`OCIThreadIdSame()` | Determines if two thread IDs represent the same thread.
`OCIThreadIdNull()` | Determines if a thread ID is `NULL`.

#### OCIThreadMutex

The `OCIThreadMutex` datatype is used to represent a mutex. This mutex is used to ensure that:

- only one thread accesses a given set of data at a time, or

- only one thread executes a given critical section of code at a time

Mutex pointers can be declared as parts of client structures or as standalone variables. Before they can be used, they must be initialized using `OCIThreadMutexInit()`. Once they are no longer needed, they must be destroyed using `OCIThreadMutexDestroy()`.

A thread can acquire a mutex by using `OCIThreadMutexAcquire()`. This ensures that only one thread at a time is allowed to hold a given mutex. A thread that holds a mutex can release it by calling `OCIThreadMutexRelease()`.

#### OCIThreadKey

The datatype `OCIThreadKey` can be thought of as a process-wide variable with a thread-specific value. This means that all threads in a process can use a given key, but each thread can examine or modify that key independently of the other threads. The value that a thread sees when it examines the key will always be the same as the value that it last set for the key. It will not see any values set for the key by other threads. The datatype of the value held by a key is a `dvoid *` generic pointer.

Keys can be created using `OCIThreadKeyInit()`. Key value are initialized to `NULL` for all threads.

A thread can set a key's value using `OCIThreadKeySet()`. A thread can get a key's value using `OCIThreadKeyGet()`.

The `OCIThread` key functions will save and retrieve data specific to the thread. When clients maintain a pool of threads and assign them to different tasks, it may not be appropriate for a task to use `OCIThread` key functions to save data associated with it.

Here is a scenario of how things can fail: A thread is assigned to execute the initialization of a task. During initialization, the task stores data in the thread using `OCIThread` key functions. After initialization, the thread is returned back to the threads pool. Later, the threads pool manager assigns another thread to perform some operations on the task, and the task needs to retrieve the data it stored earlier in initialization. Since the task is running in another thread, it will not be able to retrieve the same data. Application developers that use thread pools have to be aware of this.

#### OCIThreadKeyDestFunc

`OCIThreadKeyDestFunc` is the type of a pointer to a key's destructor routine. Keys can be associated with a destructor routine when they are created using `OCIThreadKeyInit()`. A key's destructor routine will be called whenever a thread with a non-`NULL` value for the key terminates. The destructor routine returns nothing and takes one parameter, the value that was set for key when the thread terminated.

The destructor routine is guaranteed to be called on a thread's value in the key after the termination of the thread and before process termination. No more precise guarantee can be made about the timing of the destructor routine call; no code in the process may assume any post-condition of the destructor routine. In particular, the destructor is not guaranteed to execute before a join call on the terminated thread returns.

#### OCIThreadId

`OCIThreadId` datatype is used to identify a thread. At any given time, no two threads will ever have the same `OCIThreadId`, but `OCIThreadId` values can be recycled; once a thread dies, a new thread may be created that has the same `OCIThreadId` value. In particular, the thread ID must uniquely identify a thread `T` within a process, and it must be consistent and valid in all threads `U` of the process for which it can be guaranteed that `T` is running concurrently with `U`. The thread ID for a thread `T` must be retrievable within thread `T`. This is done using `OCIThreadIdGet()`.

The `OCIThreadId` type supports the concept of a `NULL` thread ID: the `NULL` thread ID will never be the same as the ID of an actual thread.

### Active Threading Primitives

The active threading primitives deal with manipulation of actual threads. Because specifications of most of these primitives require multiple threads, they work correctly only in the enabled `OCIThread`; In the disabled `OCIThread`, they always return an error. The exception is `OCIThreadHandleGet()`; it may be called in a single-threaded environment and has no effect.

Active primitives can only be called by code running in a multithreaded environment. You can call `OCIThreadIsMulti()` to determine whether the environment is multithreaded or single-threaded. Table 9-3 lists functions used to implement active threading.

Table 9-3 Active Threading Primitives

Function | Purpose
--- | ---
`OCIThreadHndInit()` | Allocates and initializes a thread handle.
`OCIThreadHndDestroy()` | Destroys and deallocates a thread handle.
`OCIThreadCreate()` | Creates a new thread.
`OCIThreadJoin()` | Allows the calling thread to join with another.
`OCIThreadClose()` | Closes a thread handle.
`OCIThreadHandleGet()` | Retrieves a thread handle.

#### OCIThreadHandle

Datatype `OCIThreadHandle` is used to manipulate a thread in the active primitives: `OCIThreadJoin()` and `OCIThreadClose()`. A thread handle opened by `OCIThreadCreate()` must be closed in a matching call to `OCIThreadClose()`. A thread handle is invalid after the call to `OCIThreadClose()`.

## Connection Pooling in OCI

Connection pooling is the use of a group (the pool) of reusable physical connections by several sessions, in order to balance loads. The management of the pool is done by OCI, not the application. Applications that can use connection pooling include middle-tier applications for Web application servers and e-mail servers.

A sample usage of this feature is in a Web application server connected to a back-end Oracle database. Suppose that a Web application server gets several concurrent requests for data from the database server. The application can create a pool (or a set of pools) in each environment during application initialization.

### OCI Connection Pooling Concepts

Oracle has several transaction monitor capabilities such as the fine-grained management of database sessions and connections. This is done by separating the notion of database sessions (user handles) from connections (server handles). Using these OCI calls for session switching and session migration, it is possible for an application server or transaction monitor to multiplex several sessions over fewer physical connections, thus achieving a high degree of scalability by pooling of connections and back-end Oracle server processes.

The connection pool itself is normally configured with a shared pool of physical connections, translating to a back-end server pool containing an identical number of dedicated server processes.

The number of physical connections is less than the number of database sessions in use by the application.The number of physical connections and back-end server processes are also reduced by using connection pooling. Thus many more database sessions can be multiplexed.

#### Similarities and Differences from Shared Server

Connection pooling on the middle-tier is similar to what shared server offers on the back end. Connection pooling makes a dedicated server instance behave like a shared server instance by managing the session multiplexing logic on the middle tier.

The pooling of dedicated server processes including incoming connections into the dedicated server processes is controlled by the connection pool on the middle tier. The main difference between connection pooling and a shared server is that in the latter case, the connection from the client is normally to a dispatcher in the database instance. The dispatcher is responsible for directing the client request to an appropriate shared server. On the other hand, the physical connection from the connection pool is established directly from the middle-tier to the dedicated server process in the back-end server pool.

Connection pooling is beneficial only if the middle tier itself is multithreaded. Each thread can maintain a session to the database. The actual connections to the database are maintained by the connection pool and these connections (including the pool of dedicated database server processes) are shared among all the threads in the middle tier.

#### Stateless Sessions Versus Statefull Sessions

Stateless sessions are serially reusable across mid-tier threads. After a thread is done processing a database request on behalf of a three-tier user, the same database session can be reused for the purpose of a completely different request on behalf of a completely different three-tier user.

Statefull sessions to the database, on the other hand, are not serially reusable across mid-tier threads because they may have some particular state associated with a particular three-tier user. Examples of such state may be: open transactions, fetch state from a statement, PL/SQL package state, and so on. This makes the session not reusable for a different request for the duration that such state persists.

Note: Stateless sessions too may have open transactions, open statement fetch state, and so on. However, such a state persists for a relatively short duration (only during the processing of a particular three-tier request by a mid-tier thread) which allows the session to be serially reused for a different three-tier user (when such state is cleaned up).

Note: Stateless sessions are typically used in conjunction with Statement Caching.

What connection pooling offers is stateless connections and statefull sessions. Users who need to work with stateless sessions, see "Session Pooling in OCI".

#### Multiple Connection Pools

This advanced concept can be used for different database connections. Multiple connection pools can also be used when different priorities are assigned to users. Different service level guarantees can be implemented using connection pooling.

The following figure illustrates connection pooling:

Figure 9-1 OCI Connection Pooling

Description of "Figure 9-1 OCI Connection Pooling"

#### Transparent Application Failover

Transaction Application Failover (TAF) is enabled for connection pooling. The concepts of TAF apply equally well with connections in the connection pool except that the `BACKUP` and `PRECONNECT` clauses should not be used in the connect string and do not work with connection pooling and TAF.

When a connection in the connection pool fails over, it uses the primary connect string itself to connect. Sessions failover when they use the pool for a database round trip after their instance failure. The listener would be configured to route it to a good instance if available, as is typical with service-based connect strings.

See Also: Oracle Database Net Services Reference for definitions of clauses used. See chapter "Local Naming Parameters (tnsnames.ora)", "Local Naming parameters", "Connect Data Section", "fail-over."

### OCI Calls for Connection Pooling

The steps in using connection pooling in your application are:

- Allocate the Pool Handle

- Create the Connection Pool

- Logon to the Database

- Deal with SGA Limitations in Connection Pooling

- Logoff from the Database

- Destroy the Connection Pool

- Free the Pool Handle

#### Allocate the Pool Handle

Connection pooling requires that the pool handle `OCI_HTYPE_CPOOL` be allocated by `OCIHandleAlloc()`. Multiple pools can be created for a given environment handle.

For a single connection pool, here is an allocation example:

    OCICPool *poolhp;
    OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_CPOOL,
                          (size_t) 0, (dvoid **) 0));

#### Create the Connection Pool

The function OCIConnectionPoolCreate() initializes the connection pool handle. It has these `IN` parameters:

- `connMin`, the minimum number of connections to be opened when the pool is created.

- `connIncr`, the incremental number of connections to be opened when all the connections are busy and a call needs a connection. This increment is used only when the total number of open connections is less than the maximum number of connections that can be opened in that pool.

- `connMax`, the maximum number of connections that can be opened in the pool. When the maximum number of connections are open in the pool, and all the connections are busy, if a call needs a connection, it will wait till it gets one. However, if the `OCI_ATTR_CONN_NOWAIT` attribute is set for the pool, an error is returned.

- A `poolUsername`, and a `poolPasswd`, to allow user sessions to transparently migrate between connections in the pool.

- In addition, an attribute `OCI_ATTR_CONN_TIMEOUT`, can be set to time out the connections in the pool. Connections idle for more than this time are terminated periodically, to maintain an optimum number of open connections. If this attribute is not set, then the connections are never timed out.

Note: Shrinkage of the pool only occurs when there is a network round trip. If there are no operations, then the connections stay alive.

All the preceding attributes can be configured dynamically. So the application has the flexibility of reading the current load (number of open connections and number of busy connections) and tuning these attributes appropriately.

If the pool attributes (`connMax`, `connMin`, `connIncr`) are to be changed dynamically, `OCIConnectionPoolCreate()` must be called with `mode` set to `OCI_CPOOL_REINITIALIZE`.

The OUT parameters `poolName` and `poolNameLen` will contain values to be used in subsequent `OCIServerAttach()` and `OCILogon2()` calls in place of the database name and the database name length arguments.

There is no limit on the number of pools that can be created by an application. Middle tier applications can take advantage of this feature and create multiple pools to connect to the same server or to different servers, to balance the load based on the specific needs of the application.

Here is an example of this call:

    OCIConnectionPoolCreate((OCIEnv *)envhp,
                       (OCIError *)errhp, (OCICPool *)poolhp,
                       &poolName, &poolNameLen,
                       (text *)database,strlen(database),
                       (ub4) conMin, (ub4) conMax, (ub4) conIncr,
                       (text *)pooluser,strlen(pooluser),
                       (text *)poolpasswd,strlen(poolpasswd),
                       OCI_DEFAULT));

#### Logon to the Database

The application will need to log on to the database for each thread, using one of the following interfaces.

- `OCILogon2()`

  This is the simplest interface. Use this interface when you need a simple Connection Pool connection and do not need to alter any attributes of the session handle. This interface can also be used to make proxy connections to the database.

  Here is an example using `OCILogon2()`:

      for (i = 0; i < MAXTHREADS; ++i)
      {
         OCILogon2(envhp, errhp, &svchp[i], "hr", 2, "hr", 2, poolName,
                   poolNameLen, OCI_LOGON2_CPOOL));

      }

  In order to use this interface to get a proxy connection, set the password parameter to NULL.

- `OCISessionGet()`

  This is the recommended interface. It gives the user the additional option of using external authentication methods, such as certificates, distinguished name, and so on. `OCISessionGet()` is the recommended uniform function call to retrieve a session.

  Here is an example using `OCISessionGet()`:

      for (i = 0; i < MAXTHREADS; ++i)
      {
              OCISessionGet(envhp, errhp, &svchp, authp,
                            (OraText *) poolName,
                            strlen(poolName), NULL, 0, NULL, NULL, NULL,
                            OCI_SESSGET_CPOOL)
       }

- `OCIServerAttach()` and `OCISessionBegin()`:

  Another interface can be used if the application needs to set any special attributes on the user session handle and server handle. For such a requirement, applications need to allocate all the handles (connection pool handle, server handles, session handles and service context handles).

  - Create the connection pool.

  - Call `OCIServerAttach()` with mode set to `OCI_CPOOL`.

  - Call `OCISessionBegin()` with mode set to `OCI_DEFAULT`.

The user should not set `OCI_MIGRATE` flag in the call to `OCISessionBegin()`, when the virtual server handle points to a connection pool (`OCIServerAttach()` called with `mode` set to `OCI_CPOOL`). Oracle supports passing this flag, `OCI_MIGRATE`, only for compatibility reasons. Do not use the `OCI_MIGRATE` flag, because the perception that the user gets when using a connection pool is of sessions having their own dedicated (virtual) connections which are transparently multiplexed onto real connections. Credentials can be set to `OCI_CRED_RDBMS` or `OCI_CRED_PROXY`. If the credentials are set to `OCI_CRED_PROXY`, only user name needs to be set on the session handle. (no explicit primary session needs to be created and `OCI_ATTR_MIGSESSION` need not be set).

Connection pooling does the multiplexing of a virtual server handle over physical connections transparently, hence eliminating the need for users to do so. The user gets the feeling of a session having a dedicated (virtual) connection. Since the multiplexing is done transparently to the user, Users should not attempt to multiplex sessions over the virtual server handles themselves. The concepts of session migration and session switching, which require explicit multiplexing at the user level, are defunct in the case of connection pooling and should not be used.

In an OCI program, the user should create (`OCIServerAttach()` with mode set to `OCI_CPOOL`) a unique virtual server handle for each session that is created using the connection pool. There should be a one-to-one mapping between virtual server handles and sessions.

#### Deal with SGA Limitations in Connection Pooling

With `OCI_CPOOL` mode (connection pooling), the session memory (UGA) in the back-end database will come out of the SGA. This may require some SGA tuning on the back-end database to have a larger SGA if your application consumes more session memory than the SGA can accommodate. The memory tuning requirements for the back-end database will be similar to configuring the LARGE POOL in case of a shared server back end except that the instance is still in dedicated mode.

See Also: Oracle Database Performance Tuning Guide for more information, see the section on configuring Shared Server

If you are still running into the SGA limitation, you must consider:

- Reducing the session memory consumption by having fewer open statements for each session

- reducing the number of sessions in the back end by pooling sessions on the mid-tier or otherwise

- turning off connection pooling

The application must avoid using dedicated database links on the back end with connection pooling.

If the back end is a dedicated server, effective connection pooling will not be possible because sessions using dedicated database links will be tied to a physical connection rendering that same connection unusable by other sessions. If your application uses dedicated database links and you do not see effective sharing of back-end processes among your sessions, you must consider using shared database links.

See Also: For more information about distributed databases, see the section on shared database links in Oracle Database Administrator's Guide

#### Logoff from the Database

Corresponding to the logon calls, these are the interfaces to use to log off from the database in connection pooling mode.

- `OCILogoff()`:

  If `OCILogon2()` was used to make the connection, `OCILogoff()` must be used to log off.

- `OCISessionRelease()`

  If `OCISessionGet()` was called to make the connection, then `OCISessionRelease()` must be called to log off.

- `OCISessionEnd(`) and `OCIServerDetach()`

  If `OCIServerAttach()` and `OCISessionBegin()` were called to make the connection and start up the session, then `OCISessionEnd()` must be called to end the session and `OCIServerDetach()` must be called to release the connection.

#### Destroy the Connection Pool

Use OCIConnectionPoolDestroy() to destroy the connection pool.

#### Free the Pool Handle

The pool handle is freed using `OCIHandleFree()`.

These last three actions are illustrated in this code fragment:

    for (i = 0; i < MAXTHREADS; ++i)
      {
        checkerr(errhp, OCILogoff((dvoid *) svchp[i], errhp));
      }
      checkerr(errhp, OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
      checkerr(errhp, OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_CPOOL));

See Also: "Connection Pool Handle Attributes" "OCIConnectionPoolCreate()", "OCILogon2()", and "OCIConnectionPoolDestroy()"

### Examples of OCI Connection Pooling

Examples of connection pooling in tested complete programs can be found in c`democp.c and cdemocpproxy.c` in directory `demo`.

## Session Pooling in OCI

Session pooling means that the application will create and maintain a group of stateless sessions to the database. These sessions will be handed over to thin clients as requested. If no sessions are available, a new one may be created. When the client is done with the session, the client will release it to the pool. Thus, the number of sessions in the pool can increase dynamically.

Some of the sessions in the pool may be 'tagged' with certain properties. For instance, a user may request for a default session, set certain attributes on it, then label it or 'tag' it and return in to the pool. That user, or some other user, can require a session with the same attributes, and thus request for a session with the same tag. There may be several sessions in the pool with the same tag. The 'tag' on a session can be changed or reset.

See Also: "Using Tags in Session Pools"

Proxy sessions, too, can be created and maintained through this interface.

The behavior of the application when no free sessions are available and the pool has reached it's maximum size, will depend on certain attributes. A new session may be created or an error returned, or the thread may just block and wait for a session to become free.

The main benefit of this type of pooling will be performance. Making a connection to the database is a time-consuming activity, especially when the database is remote. Thus, instead of a client spending time connecting to the server, authenticating its credentials, and then receiving a valid session, it can just pick one from the pool.

### Functionality of OCI Session Pooling

Session pooling has the following features:

- Create, maintain and manage a pool of stateless sessions transparently.

- Provide an interface for the application to create a pool and specify the minimum, increment and maximum number of sessions in the pool.

- Provide an interface for the user to obtain and release a default or 'tagged' session to the pool. A 'tagged' session is one with certain client-defined properties.

- Allow the application to dynamically change the number of minimum and maximum number of sessions.

- Provide a mechanism to always maintain an optimum number of open sessions, by closing sessions that have been idle for very long, and creating sessions when required.

- Allow for session pooling with authentication.

### Homogeneous and Heterogeneous Session Pools

A session pool can be either homogeneous or heterogeneous. Homogeneous session pooling means that sessions in the pool are alike with respect to authentication (have the same user name and password and privileges). Heterogeneous session pooling means that you must provide authentication information because the sessions can have different security attributes and privileges.

### Using Tags in Session Pools

The tags provide a way for users to customize sessions in the pool. A client may get a default or untagged session from a pool, set certain attributes on the session (such as NLS settings), and return the session to the pool, labeling it with an appropriate tag in the `OCISessionRelease()` call.

The user, or some other user, may request a session with the same tags in order to have a session withe the same attributes, and can do so by providing the same tag in the `OCISessionGet()` call.

See Also: "OCISessionGet()" for a further discussion of tagging sessions.

### OCI Handles for Session Pooling

Two handle types have been added for session pooling:

#### OCISPool

This is the session pool handle. It is allocated using `OCIHandleAlloc()`. It needs to be passed to `OCISessionPoolCreate()`, and OCISessionPoolDestroy(). It has the attribute type `OCI_HTYPE_SPOOL`.

An example of the `OCIHandleAlloc()` call follows:

    OCISPool *spoolhp;
    OCIHandleAlloc((dvoid *) envhp, (dvoid **) &spoolhp, OCI_HTYPE_SPOOL,
                          (size_t) 0, (dvoid **) 0));

For an environment handle, multiple session pools can be created.

#### OCIAuthInfo

This is the authentication information handle. It is allocated using `OCIHandleAlloc()`. It is passed to `OCISessionGet()`. It supports all the attributes that are supported for user session handle. Please refer to user session handle attributes for more information. The authentication information handle has the attribute type `OCI_HTYPE_AUTHINFO`.

An example of the `OCIHandleAlloc()` call follows:

    OCIAuthInfo *authp;
    OCIHandleAlloc((dvoid *) envhp, (dvoid **) &authp, OCI_HTYPE_AUTHINFO,
                          (size_t) 0, (dvoid **) 0));

See Also: "User Session Handle Attributes" for the attributes that belong to the authentication information handle. "Session Pool Handle Attributes" for more information about the session pooling attributes. "Connect, Authorize, and Initialize Functions" for complete information about the functions used in session pooling. See "OCISessionGet()" for details of the session handle attributes that can be used with this call.

### Using OCI Session Pooling

The steps in writing a simple session pooling application which uses a user name and password are:

- Allocate the session pool handle using `OCIHandleAlloc()` for an `OCISPool` handle. Multiple session pools can be created for an environment handle.

- Create the session pool using OCISessionPoolCreate() with `mode` set to `OCI_DEFAULT` (for a new session pool). Refer to the function for a discussion of the other modes.

- Loop for each thread. Create the thread with a function that does the following:

- Allocate an authentication information handle of type `OCIAuthInfo` using `OCIHandleAlloc()`.

- Set the user name and password in the authentication information handle using `OCIAttrSet()`.

- Get a pooled session using OCISessionGet() with `mode` set to `OCI_SESSGET_SPOOL`.

- Perform the transaction.

- Commit or rollback the transactions.

- Release the session (logoff) with OCISessionRelease().

- Free the authentication information handle with `OCIHandleFree()`.

- End of the loop for each thread.

- Destroy the session pool using OCISessionPoolDestroy().

### OCI Calls for Session Pooling

Here are the usages for OCI calls for session pooling.

#### Allocate the Pool Handle

Session pooling requires that the pool handle `OCI_HTYPE_SPOOL` be allocated by calling `OCIHandleAlloc()`.

Multiple pools can be created for a given environment handle. For a single session pool, here is an allocation example:

    OCISPool *poolhp;
    OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_SPOOL, (size_t) 0,
                   (dvoid **) 0));

#### Create the Pool Session

The function `OCISessionPoolCreate()` can be used to create the session pool. Here is an example of how to use this call:

    OCISessionPoolCreate(envhp, errhp, poolhp, (OraText **)&poolName,
                  (ub4 *)&poolNameLen, database,
                  (ub4)strlen((const signed char *)database),
                  sessMin, sessMax, sessIncr,
                  (OraText *)appusername,
                  (ub4)strlen((const signed char *)appusername),
                  (OraText *)apppassword,
                  (ub4)strlen((const signed char *)apppassword),
                  OCI_DEFAULT);

#### Logon to the Database

These are the interfaces that can be used to logon to the database in session pooling mode.

- `OCILogon2()`:

  This is the simplest interface. However, it does not give the user the option of using tagging. Here is an example of how `OCILogon2()` can be used to log on to the database in session pooling mode:

      for (i = 0; i < MAXTHREADS; ++i)
      {
        OCILogon2(envhp, errhp, &svchp[i], "hr", 2, "hr", 2, poolName,
                  poolNameLen, OCI_LOGON2_SPOOL));

      }

- `OCISessionGet()`:

  This is the recommended interface. It gives the user the option of using tagging to label sessions in the pool, and thus make it easier to retrieve specific sessions. An example of using `OCISessionGet()` follows. It is taken from `cdemosp.c` in the `demo` directory.:

      OCISessionGet(envhp, errhp, &svchp, authInfop,
                   (OraText *)database,strlen(database), tag,
                   strlen(tag), &retTag, &retTagLen, &found,
                   OCI_SESSGET_SPOOL);

#### Logoff from the Database

Corresponding to the preceding logon calls, these are the interfaces to use to log off from the database in session pooling mode.

- `OCILogoff()`:

  If `OCILogon2()` was used to make the connection, `OCILogoff()` must be used to log off.

- `OCISessionRelease()`

  If `OCISessionGet()` was called to make the connection, then `OCISessionRelease()` must be called to log off.

#### Destroy the Session Pool

`OCISessionPoolDestroy()` must be called to destroy the session pool. Here is an example of how this call can be made:

    OCISessionPoolDestroy(poolhp, errhp, OCI_DEFAULT);

#### Free the Pool Handle

`OCIHandleFree()` must be called to free the session pool handle. Here is how this call can be made:

    OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_SPOOL);

Note: The application has to ensure either a commit or rollback is done before a session is released to the session pool. OCI does not reset the state of the session.

### Example of OCI Session Pooling

Here is an example of session pooling in a tested, complete program:

See Also: `cdemosp.c` in directory `demo`

## When to Use Connection Pooling, Session Pooling, or Neither

If database sessions are not reusable by mid-tier threads (that is, they are statefull) and the number of back-end server processes may cause scaling problems on the database, use OCI connection pooling.

If database sessions are reusable by mid-tier threads (that is, they are stateless) and the number of back-end server processes may cause scaling problems on the database, use OCI session pooling.

If database sessions are not reusable by mid-tier threads (that is, they are statefull) and the number of back-end server processes will never be large enough to potentially cause any scaling issue on the database, there is no need to use any pooling mechanism.

Note: Having non-pooled sessions/connections will result in tearing down and recreation of the database session/connection for every mid-tier user request. This can cause severe scaling problems on the database side and excessive latency for the fulfillment of the request. Hence, it is strongly recommended that one of the pooling strategies be adopted for mid-tier applications based on whether the database session is statefull or stateless.

In connection pooling, the pool element is a connection and in session pooling, the pool element is a session.

As with any pool, the pooled resource is locked by the application thread for a certain duration until the thread has done its job on the database and the resource is released. The resource is unavailable to other threads during its period of use. Hence, application developers need to be aware that any kind of pooling works effectively with relatively short tasks. If the application is performing a long running transaction for example, it may deny the pooled resource to other sharers for long periods of time leading to starvation. Hence, pooling should be used in conjunction with short tasks and the size of the pool should be sufficiently large to maintain the desired concurrency of transactions.

Also, note that with:

1. OCI Connection Pool

   1. Connections to the database are pooled. Sessions are created and destroyed by the user. Each call to the database will pick up an appropriate available connection from the pool.

   2. The application is multiplexing several sessions over fewer physical connections to the database. The users can tune the pool configuration to achieve required concurrency.

   3. The life-time of the application sessions is independent of the life-time of the cached pooled connections.

2. OCI Session Pool

   Sessions and connections are pooled by OCI. The application gets sessions from the pool and release sessions back to the pool.

### Functions for Session Creation

The choices are:

1. `OCILogon()`

   This is the simplest way to get an OCI Session. The advantage is ease of obtaining an OCI service context. The disadvantage is that you cannot perform any advance OCI operations like session migration, proxy authentication, using a connection pool, or a session pool.

   See Also: "Application Initialization, Connection, and Session Creation"

2. `OCILogon2()`

   This includes the functionality of `OCILogon()` to get a session. This session may be a new one with a new underlying connection, or one that is started over a virtual connection from an existing connection pool, or one from an existing session pool. The `mode` parameter value that the function is called with determines its behavior.

   The user cannot modify the attributes (except `OCI_ATTR_``STMTCACHESIZE`) of the service context returned by OCI.

   See Also: "OCILogon2()"

3. `OCISessionBegin()`

   This supports all the various options of an OCI session such as proxy authentication, getting a session from a connection pool or a session pool, external credentials, and migratable sessions. This is the lowest level call where all handles are needed to be explicitly allocated and all attributes set, and `OCIServerAttach()` is to be called prior to this call.

   See Also: "OCISessionBegin()"

4. `OCISessionGet()`

   This is now the recommended method to get a session. This session may be a new one with a new underlying connection, or one that is started over a virtual connection from an existing connection pool, or one from an existing session pool. The `mode` parameter value that the function is called with determines its behavior. This works like `OCILogon2()` but additionally enables you to specify tags for obtaining specific sessions from the pool.

   See Also: "OCISessionGet()"

### Choosing Between Different Types of OCI Sessions

The choices are:

- Basic OCI Sessions

  This works by using user name and password over a dedicated OCI server handle. This is the no-pool mechanism. See earlier notes of when to use it.

  If authentication is obtained through external credentials, then user name or password is not required.

- Session Pool Sessions

  These sessions are from the session pool cache. Some sessions may be tagged. These are stateless sessions. Each `OCISessionGet()` and `OCISessionRelease()` call gets and releases a session from the session cache. This saves the server from creating and destroying sessions.

  See the earlier notes on connection pool sessions versus session pooling sessions versus no-pooling sessions.

- Connection Pool Sessions

  These are sessions created using `OCISessionGet()` and `OCISessionBegin()` calls from an OCI Connection Pool. There is no session cache as these are statefull sessions. Each call creates a new session and the user is responsible for terminating these sessions.

  The sessions are automatically migratable between the server handles of the connection pool. Each session can have user name and password or be a proxy session. See the earlier notes on connection pool sessions versus session pooling sessions versus no-pooling sessions.

- Sessions Sharing a Server Handle

  You can multiplex several OCI Sessions over a few physical connections. The application does this manually by having the same server handle for these multiple sessions. It is preferred to have the session multiplexing details be left to OCI by using the OCI Connection Pool APIs.

- Proxy Sessions

  This is useful if the password of the client needs to be protected from the middle-tier. Proxy sessions can also be part of OCI Connection Pool and OCI Session Pool.

  See Also: "Middle-Tier Applications in OCI"

- Migratable Sessions

With transaction handles being migratable, there should be no need for applications to use this older feature, in light of OCI Connection Pooling.

See Also: "OCI Session Management"

## Statement Caching in OCI

Statement caching refers to the feature that provides and manages a cache of statements for each session. In the server, it means that cursors are ready to be used without the need to parse the statement again. Statement caching can be used with connection pooling and with session pooling, and will improve performance and scalability. It can be used without session pooling as well. The OCI calls that implement statement caching are:

- `OCIStmtPrepare2()`

- `OCIStmtRelease()`

### Statement Caching without Session Pooling in OCI

Users perform the usual OCI steps to logon. The call to obtain a session will have a mode that specifies whether statement caching is enabled for the session. Initially the statement cache will be empty. Developers will try to find a statement in the cache using the statement text. If the statement exists the API will return a previously prepared statement handle, otherwise it will return an newly prepared statement handle.

The application developer can perform binds and defines and then simply execute and fetch the statement before returning the statement back to the cache. In the latter case, where the statement handle was not found, the developer will need to set different attributes on the handle in addition to the other steps.

`OCIStmtPrepare2()` will also take a mode which will determine if the developer wants a prepared statement handle or a null statement handle if the statement is not found in the cache.

The pseudo code will look like:

    OCISessionBegin( userhp, ... OCI_STMT_CACHE)  ;
    OCIAttrset(svchp, userhp, ...);  /* Set the user handle in the service context */
    OCIStmtPrepare2(svchp, &stmthp, stmttext, key, ...);
    OCIBindByPos(stmthp, ...);
    OCIDefineByPos(stmthp, ...);
    OCIStmtExecute(svchp, stmthp, ...);
    OCIStmtFetch(svchp, ...);
    OCIStmtRelease(stmthp, ...);
    ...

### Statement Caching with Session Pooling in OCI

The concepts remain the same, except that the statement cache is enabled at the session pool layer rather than at the session layer.

The attribute `OCI_ATTR_SPOOL_STMTCACHESIZE` sets the default statement cache size for each of the sessions in the session pool to this value. It is set on the `OCI_HTYPE_SPOOL` handle. The statement cache size for a particular session in the pool can anytime be overridden by using OCI_ATTR_STMTCACHESIZE on that session. The value of `OCI_ATTR_SPOOL_STMTCACHESIZE` can be changed at any time. This attribute can be used to enable or disable statement caching at the pool level, after creation, just as attribute `OCI_ATTR_STMTCACHESIZE` (on the service context) is used to enable or disable statement caching at the session level. This change will be reflected on individual sessions in the pool, when they are handed to a user. Tagged sessions are an exception to this behavior. This is explained later.

Enabling or disabling of statement caching is allowed on individual pooled sessions similar to non-pooled sessions.

A user can enable statement caching on a session retrieved from a non-statement cached pool in an `OCISessionGet()` or `OCILogon2()` call by specifying `OCI_SESSGET_STMTCACHE` or `OCI_LOGON2_STMTCACHE`, respectively, in the mode argument.

When a user asks for a session from a session pool, the statement cache size for that session will default to that of the pool. This may also mean enabling or disabling statement caching in that session. For example, if a pooled session (session A) has statement caching enabled, and statement caching is turned off in the pool, and a user asks for a session, and session A is returned, then statement caching will be turned off in Session A. As another example, if Session A in a pool does not have statement caching enabled, and statement caching at the pool level is turned on, then before returning session A to a user, statement caching on Session A with size equal to that of the pool is turned on.

This will not hold true if a tagged session is asked for and retrieved. In this case, the size of the statement cache will not be changed. Consequently, it will not be turned on or off. Moreover, if the user specifies mode `OCI_SESSGET_STMTCACHE` in the `OCISessionGet()` call, this will be ignored if the session is tagged. In our earlier example, if Session A was tagged, then it is returned as is to the user.

### Rules for Statement Caching in OCI

Here are some rules to follow:

- Use the function `OCIStmtPrepare2()` instead of `OCIStmtPrepare()`. If you are using `OCIStmtPrepare()`, you are strongly urged not to use a statement handle across different service contexts. Doing so will raise an error if the statement has been obtained by `OCIStmtPrepare2()`. Migration of a statement handle to a new service context actually closes the cursor associated with the old session and therefore no sharing is achieved. Client-side sharing is also not obtained, because OCI will free all buffers associated with the old session when the statement handle is migrated.

- You are urged to keep one service context for each session and use statement handles only for that service context. That will be the preferred and recommended model and usage.

- A call to `OCIStmtPrepare2()`, even if the session does not have a statement cache, will also allocate the statement handle and therefore applications using only `OCIStmtPrepare2()` must not call `OCIHandleAlloc()` for the statement handle.

- A call to the `OCIStmtPrepare2()` must be followed with `OCIStmtRelease()` after the user is done with the statement handle. If statement caching is used, this will release the statement to the cache. If statement caching is not used, the statement will be deallocated. Do not call `OCIHandleFree()` to free the memory.

- If the call to `OCIStmtPrepare2()` is made with the `OCI_PREP2_CACHE_SEARCHONLY` mode and a NULL statement was returned (statement was not found), the subsequent call to `OCIStmtRelease()` is not required and must not be performed.

- Do not call `OCIStmtRelease()` for a statement that was prepared using `OCIStmtPrepare()`.

- The statement cache has a maximum size (number of statements) which can be modified by an attribute on the service context, `OCI_ATTR_STMTCACHESIZE`. The default value is 20. This attribute can also be used to enable or disable statement caching for the session, pooled or non-pooled. If `OCISessionBegin()` is called without mode set as `OCI_STMT_CACHE`, then `OCI_ATTR_STMTCACHESIZE` can be set on the service context to a nonzero attribute to turn on statement caching. If statement caching is not turned on at the session pool level, `OCISessionGet()` will return a non-statement cache-enabled session. `OCI_ATTR_STMTCACHESIZE` can be used to turn the caching on. Similarly the same attribute can be used to turn off statement caching by setting the cache size to zero.

- You can choose to tag a statement at the release time so that the next time you can request a statement of the same tag. The tag will be used to search the cache. An untagged statement (tag is `NULL`) is a special case of a tagged statement. Two statements are considered different if they only differ in their tags, or if one is untagged and the other is not.

  See Also: For information about the functions for statement caching, "Statement Functions" "Service Context Handle Attributes" "Session Pool Handle Attributes"

### OCI Statement Caching Code Example

Here is an example of statement caching:

See Also: Please refer to `ocisc.c` in directory `demo` for a working example of statement caching.

## User-Defined Callback Functions in OCI

The Oracle Call Interface has the capability to execute user-specific code in addition to OCI calls. This functionality can be used for:

- Adding tracing and performance measurement code to enable users to tune their applications.

- Performing pre- or post-processing code for specific OCI calls.

- Accessing other data sources with OCI by using the native OCI interface for Oracle databases and directing the OCI calls to use user callbacks for non-Oracle data sources.

The OCI callback feature has been added by providing support for calling user code before or after executing the OCI calls. Functionality has also been provided to allow the user-defined code to be executed instead of executing the OCI code.

The user callback code can also be registered dynamically without modifying the source code of the application. The dynamic registration is implemented by loading up to five user-created dynamically linked libraries after the initialization of the environment handle during the `OCIEnvCreate()` call. These user-created libraries (such as Dynamic Link Libraries (DLLs) on Windows, or shared libraries on Solaris register the user callbacks for the selected OCI calls transparently to the application.

Sample Application

For a listing of the complete demonstration programs that illustrate the OCI user callback feature, see Appendix B, "OCI Demonstration Programs".

### Registering User Callbacks in OCI

An application can register user callback libraries with the `OCIUserCallbackRegister()` function. Callbacks are registered in the context of the environment handle. An application can retrieve information about callbacks registered with a handle with the `OCIUserCallbackGet()` function.

See Also: "OCIUserCallbackGet()" and "OCIUserCallbackRegister()"

A user-defined callback is a subroutine that is registered against an OCI call and an environment handle. It can be specified to be either an entry callback, a replacement callback, or an exit callback.

- If it is an entry callback, it is called when the program enters the OCI function.

- Replacement callbacks are executed after entry callbacks. If the replacement callback returns a value of `OCI_CONTINUE`, then a subsequent replacement callback or the normal OCI-specific code is executed. If a replacement callback returns anything other than `OCI_CONTINUE`, subsequent replacement callbacks and the OCI code does not execute.

- After a replacement callback returns something other than `OCI_CONTINUE`, or an OCI function successfully executes, program control transfers to the exit callback (if one is registered).

If a replacement or exit callback returns anything other than `OCI_CONTINUE`, then the return code from the callback is returned from the associated OCI call.

A user callback can return `OCI_INVALID_HANDLE` when either an invalid handle or an invalid context is passed to it.

Note: If any callback returns anything other than `OCI_CONTINUE`, then that return code is passed to the subsequent callbacks. If a replacement or exit callback returns a return code other than `OCI_CONTINUE`, then the final (not `OCI_CONTINUE`) return code is returned from the OCI call.

#### OCIUserCallbackRegister

A user callback is registered using the OCIUserCallbackRegister() call.

See Also: "OCIUserCallbackRegister()"

Currently, `OCIUserCallbackRegister()` is only registered on the environment handle. The user's callback function of typedef `OCIUserCallback` is registered along with its context for the OCI call identified by the OCI function code, fcode. The type of the callback, whether entry, replacement, or exit, is specified by the when parameter.

For example, the `stmtprep_entry_dyncbk_fn` entry callback function and its context `dynamic_context`, are registered against the environment handle `hndlp` for the `OCIStmtPrepare()` call by calling the `OCIUserCallbackRegister()` function with the following parameters.

    OCIUserCallbackRegister( hndlp,
                             OCI_HTYPE_ENV,
                             errh,
                             stmtprep_entry_dyncbk_fn,
                             dynamic_context,
                             OCI_FNCODE_STMTPREPARE,
                             OCI_UCBTYPE_ENTRY
                             (OCIUcb*) NULL);

#### User Callback Function

The user callback function has to follow the following syntax:

    typedef sword (*OCIUserCallback)
         (dvoid *ctxp,      /* context for the user callback*/
          dvoid *hndlp,     /* handle for the callback, env handle for now */
          ub4 type,         /* type of handlp, OCI_HTYPE_ENV for this release */
          ub4 fcode,        /* function code of the OCI call */
          ub1 when,         /* type of the callback, entry or exit */
          sword returnCode, /* OCI return code */
          ub4 *errnop,      /* Oracle error number */
          va_list arglist); /* parameters of the oci call */

In addition to the parameters described in the `OCIUserCallbackRegister()` call, the callback is called with the return code, errnop, and all the parameters of the original OCI as declared by the prototype definition.

The return code is always passed in as `OCI_SUCCESS` and `*errnop` is always passed in as 0 for the first entry callback. Note that `*errnop` refers to the content of `errnop` because `errnop` is an IN/OUT parameter.

If the callback does not want to change the OCI return code, then it must return `OCI_CONTINUE`, and the value returned in `*errnop` is ignored. If on the other hand, the callback returns any other return code than `OCI_CONTINUE`, the last returned return code becomes the return code for the call. At the this point, the value of `*errnop` returned is set in the error handle, or in the environment handle if the error information is returned in the environment handle because of the absence of the error handle for certain OCI calls such as `OCIHandleAlloc()`.

For replacement callbacks, the `returnCode` is the non-`OCI_CONTINUE` return code from the previous callback or OCI call and `*errnop` is the value of the error number being returned in the error handle. This allows the subsequent callback to change the return code or error information if needed.

The processing of replacement callbacks is different in that if it returns anything other than `OCI_CONTINUE`, then subsequent replacement callbacks and OCI code is bypassed and processing jumps to the exit callbacks.

Note that if the replacement callbacks return `OCI_CONTINUE` to allow processing of OCI code, then the return code from entry callbacks is ignored.

All the original parameters of the OCI call are passed to the callback as variable parameters and the callback must retrieve them using the va_arg macros. The callback demonstration programs provide examples.

See Also: Appendix B, "OCI Demonstration Programs"

A null value can be registered to de-register a callback. That is, if the value of the callback (`OCIUserCallback()`) is `NULL` in the `OCIUserCallbackRegister()` call, then the user callback is de-registered.

When using the thread-safe mode, the OCI program acquires all mutexes before calling the user callbacks.

#### UserCallback Control Flow

This pseudocode describes the overall processing of a typical OCI call:

    OCIXyzCall()
    {
     Acquire mutexes on handles;
     retCode = OCI_SUCCESS;
     errno = 0;
     for all ENTRY callbacks do
      {

         EntryretCode = (*entryCallback)(..., retcode, &errno, ...);
         if (retCode != OCI_CONTINUE)
          {
             set errno in error handle or environment handle;
             retCode = EntryretCode;
           }
       }
      for all REPLACEMENT callbacks do
      {
       retCode = (*replacementCallback) (..., retcode, &errno, ...);
       if (retCode != OCI_CONTINUE)
          {
           set errno in error handle or environment handle
           goto executeEXITCallback;
           }
       }

       retCode = return code for XyzCall; /* normal processing of OCI call */

       errno = error number from error handle or env handle;

     executeExitCallback:
       for all EXIT callbacks do
       {
           exitRetCode = (*exitCallback)(..., retCode, &errno,...);
           if (exitRetCode != OCI_CONTINUE)
           {
               set errno in error handle or environment handle;
               retCode = exitRetCode;
           }
       }
        release mutexes;
        return retCode
    }

#### UserCallback for OCIErrorGet()

If the callbacks are a total replacement of the OCI code, then they usually maintain their own error information in the call context and use that to return error information in `bufp` and `errnop` parameters of the replacement callback of the `OCIErrorGet()` call.

If on the other hand, the callbacks are either partially overriding OCI code, or just doing some other post processing, then they can use the exit callback to modify the error text and `errnop` parameters of the `OCIErrorGet()` by their own error message and error number. Note that the `*errnop` passed into the exit callback is the error number in the error or the environment handle.

#### Errors from Entry Callbacks

If an entry callback wants to return an error to the caller of the OCI call, then it must register a replacement or exit callback. This is because if the OCI code is executed, then the error code from the entry callback is ignored. Therefore the entry callback must pass the error to the replacement or exit callback through its own context.

#### Dynamic Callback Registrations

Because user callbacks are expected to be used for monitoring OCI behavior or to access other data sources, it is desirable that the registration of the callbacks be done transparently and non-intrusively. This is accomplished by loading user-created dynamically linked libraries at OCI initialization time. These dynamically linked libraries are called packages. The user-created packages register the user callbacks for the selected OCI calls. These callbacks can further register or de-register user callbacks as needed when receiving control at runtime.

A makefile (`ociucb.mk` on Solaris) is provided with the OCI demonstration programs to create the package. The exact naming and location of this package is operating system dependent. The source code for the package must provide code for special callbacks that are called at OCI initialization and environment creation times.

The loading of the package is controlled by setting an operating system environment variable, `ORA_OCI_UCBPKG`. This variable names the packages in a generic way. The packages must be located in the `$ORACLE_HOME/lib` directory.

#### Loading Multiple Packages

The `ORA_OCI_UCBPKG` variable can contain a semicolon separated list of package names. The packages are loaded in the order they are specified in the list.

For example, previously one specified the package as:

    setenv ORA_OCI_UCBPKG mypkg

Now, you can still specify the package as earlier, but in addition multiple packages can be specified as:

    setenv ORA_OCI_UCBPKG "mypkg;yourpkg;oraclepkg;sunpkg;msoftpkg"

All these packages are loaded in order. That is, `mypkg` is loaded first and `msoftpkg` is loaded last.

A maximum of five packages can be specified.

Note: The sample makefile `ociucb.mk` creates `ociucb.so.1.0` on a Solaris or `ociucb.dll` on a Windows system. To load the `ociucb` package, the environmental variable `ORA_OCI_UCBPKG` must be set to `ociucb`. On Solaris, if the package name ends with `.so`, `OCIInitialize()` fails. The package name must end with `.so.1.0`. For further details about creating the dynamic link libraries, read the Makefiles provided in the demo directory for your operating system. For further information on user-defined callbacks, see your operating system-specific documentation on compiling and linking applications.

#### Package Format

Previously a package had to specify the source code for the `OCIEnvCallback()` function. Now the `OCIEnvCallback()` function is obsolete. Instead, the package source must provide two functions. The first function has to be named as packagename suffixed with the word Init. For example, if the package is named `foo`, then the source file (for example, but not necessarily, foo.c) must contain a `fooInit()` function with a call to `OCISharedLibInit()` function specified exactly as:

    sword fooInit(metaCtx, libCtx, argfmt, argc, argv)
          dvoid *         metaCtx;         /* The metacontext */
          dvoid *         libCtx;          /* The context for this package. */
          ub4             argfmt;          /* package argument format */
          sword           argc;            /* package arg count*/
          dvoid *         argv[];          /* package arguments */
    {
      return  (OCISharedLibInit(metaCtx, libCtx, argfmt, argc, argv,
                                fooEnvCallback));
    }

The last parameter of the `OCISharedLibInit()` function, `fooEnvCallback()`, in this case, is the name of the second function. It can be named anything, but by convention it can be named packagename suffixed with the word EnvCallback.

This function is a replacement for `OCIEnvCallback()`. Now all the dynamic user callbacks must be registered in this function. The function must be of type `OCIEnvCallbackType`, which is specified as:

    typedef sword (*OCIEnvCallbackType)(OCIEnv *env, ub4 mode,
                                        size_t xtramem_sz, dvoid *usrmemp,
                                        OCIUcb *ucbDesc);

When an environment handle is created, then this callback function is called at the very end. The `env` parameter is the newly created environment handle.

The `mode`, `xtramem_sz`, and `usrmemp` are the parameters passed to the `OCIEnvCreate()` call. The last parameter, `ucbDesc`, is a descriptor that is passed to the package. The package uses this descriptor to register the user callbacks as described later.

A sample `ociucb.c` file is provided in the demo directory. The makefile `ociucb.mk` is also provided (on Solaris) in the `demo` directory to create the package. Please note that this may be different on other operating systems. The demo directory also contains full user callback demo programs (`cdemoucb.c, cdemoucbl.c`,) illustrating this.

#### User Callback Chaining

User callbacks can both be registered statically in the application itself or dynamically at runtime in the DLLs. A mechanism is needed to allow the application to override a previously registered callback and then later invoke the overridden one in the newly registered callback to preserve the behavior intended by the dynamic registrations. This can result in chaining of user callbacks.

For this purpose, the `OCIUserCallbackGet()` function is provided to find out which function and context is registered for an OCI call.

See Also: "OCIUserCallbackGet()"

#### Accessing Other Data Sources Through OCI

Because Oracle is the predominant database accessed, applications can take advantage of the OCI interface to access non-Oracle data by using the user callbacks to access them. This allows an application written in OCI to access Oracle data without any performance penalty. To access non-Oracle data sources, drivers can be written that access the non-Oracle data in user callbacks. Because OCI provides a very rich interface, there is usually a straightforward mapping of OCI calls to most data sources. This solution is better than writing applications for other middle layers such as ODBC that introduce performance penalties for all data sources. Using OCI does not incur any penalty for the common case of accessing Oracle data sources, and incurs the same penalty that ODBC does for non-Oracle data sources.

#### Restrictions on Callback Functions

There are certain restrictions on the usage of callback functions, including `OCIEnvCallback()`:

- A callback cannot call other OCI functions except `OCIUserCallbackRegister()`, `OCIUserCallbackGet()`, `OCIHandleAlloc()`, `OCIHandleFree()`. Even for these functions, if they are called in a user callback, then callbacks on them are not called to avoid recursion. For example, if `OCIHandleFree()` is called in the callback for `OCILogoff()`, then the callback for `OCIHandleFree()` is disabled during the execution of the callback for `OCILogoff()`.

- A callback cannot modify OCI data structures such as the environment or error handles.

- A callback cannot be registered for `OCIUserCallbackRegister()` call itself, or for any of the following:

  - `OCIUserCallbackGet()`

  - `OCIEnvCreate()`

  - `OCIInitialize()`

  - `OCIEnvInit()`

#### Example of OCI Callbacks

For example, lets suppose that there are five packages each registering entry, replacement, and exit callbacks for the `OCIStmtPrepare()` call. That is, the `ORA_OCI_UCBPKG` variable is set as:

    setenv ORA_OCI_UCBPKG "pkg1;pkg2;pkg3;pkg4;pkg5"

In each package `pkgN` (where N can be 1 through 5), the `pkgNInit()` and `PkgNEnvCallback()` functions are specified as:

    pkgNInit(dvoid *metaCtx, dvoid *libCtx, ub4 argfmt, sword argc, dvoid **argv)
    {
      return OCISharedLibInit(metaCtx, libCtx, argfmt, argc, argv, pkgNEnvCallback);
    }

The `pkgNEnvCallback()` function registers the entry, replacement, and exit callbacks as:

    pkgNEnvCallback(OCIEnv *env, ub4 mode, size_t xtramemsz,
                                    dvoid *usrmemp, OCIUcb *ucbDesc)
    {
      OCIHandleAlloc((dvoid *)env, (dvoid **)&errh, OCI_HTYPE_ERROR, (size_t) 0,
            (dvoid **)NULL);

      OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_entry_callback_fn,
            pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_ENTRY, ucbDesc);

      OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_replace_callback_fn,
            pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_REPLACE, ucbDesc);

      OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_exit_callback_fn,
            pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_EXIT, ucbDesc);

      return OCI_CONTINUE;
    }

Finally, in the source code for the application, user callbacks can be registered with the `NULL` ucbDesc as:

    OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_entry_callback_fn,
            pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_ENTRY, (OCIUcb *)NULL);

      OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_replace_callback_fn,
            pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_REPLACE, (OCIUcb *)NULL);

      OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_exit_callback_fn,
            pkgNctx, OCI_FNCODE_STMTPREPARE, OCI_UCBTYPE_EXIT, (OCIUcb *)NULL);

When the `OCIStmtPrepare()` call is executed, the callbacks are called in the following order:

    static_entry_callback_fn()
    pkg1_entry_callback_fn()
    pkg2_entry_callback_fn()
    pkg3_entry_callback_fn()
    pkg4_entry_callback_fn()
    pkg5_entry_callback_fn()

    static_replace_callback_fn()
     pkg1_replace_callback_fn()
      pkg2_replace_callback_fn()
       pkg3_replace_callback_fn()
        pkg4_replace_callback_fn()
         pkg5_replace_callback_fn()

          OCI code for OCIStmtPrepare call

    pkg5_exit_callback_fn()
    pkg4_exit_callback_fn()
    pkg3_exit_callback_fn()
    pkg2_exit_callback_fn()
    pkg1_exit_callback_fn()

    static_exit_callback_fn()

Note: The exit callbacks are called in the reverse order of the entry and replacement callbacks

The entry and exit callbacks can return any return code and the processing continues to the next callback. However, if the replacement callback returns anything other than `OCI_CONTINUE`, then the next callback (or OCI code if it is the last replacement callback) in the chain is bypassed and processing jumps to the exit callback. For example, if `pkg3_replace_callback_fn()` returned `OCI_SUCCESS`, then `pkg4_replace_callback_fn()`, `pkg5_replace_callback_fn()`, and the OCI processing for the `OCIStmtPrepare()` call is bypassed. Instead, `pkg5_exit_callback_fn()` is executed next.

### OCI Callbacks from External Procedures

There are several OCI functions that can be used as callbacks from external procedures.

See Also: These functions are listed in Chapter 19, "OCI Cartridge Functions". For information about writing C subroutines that can be called from PL/SQL code, including a list of which OCI calls can be used, and some example code, refer to the Oracle Database Application Developer's Guide - Fundamentals

## Transparent Application Failover Callbacks in OCI

Transparent Application Failover (TAF) callbacks can be used in the event of the failure of one database instance and failover to another instance. Failover means that connections are reestablished using the same connect string or an alternate connect string specified in the application. Because of the delay which can occur during failover, the application developer may want to inform the user that failover is in progress, and request that the user stand by. Additionally, the session on the initial instance may have received some `ALTER SESSION` commands. These will not be automatically replayed on the second instance. Consequently, the developer may wish to replay these `ALTER` `SESSION` commands on the second instance.

In previous versions, TAF with the SELECT failover option would be engaged on the statement that was in use at the time of a failure. For example, if there were 10 statement handles in use by the application, and statement 7 was the failure-time statement (the statement in use when the failure happened), statements 1-6 and 8-10 would have to be re-executed after statement 7 was failed over using TAF.

Since 10g Release 2 (10.2), this has been improved. Now all statements that an application attempts to use after a failure will attempt failover. That is, an attempt to execute or fetch against other statements will engage TAF recovery just as for the failure-time statement. Notably, this means that subsequent statements may now succeed (whereas in the past they failed), or the application may receive errors corresponding to an attempted TAF recovery (like ORA-25401). However, the TAF callbacks will only be called once (during TAF recovery of the failure-time statement).

Note: TAF is not supported for remote database links. TAF is not supported for DML statements.

See Also: Oracle Database Net Services Reference for more detailed information about application failover

### Failover Callback Overview

To address the problems described earlier, the application developer can register a failover callback function. In the event of failover, the callback function is invoked several times during the course of reestablishing the user's session.

The first call to the callback function occurs when Oracle first detects an instance connection loss. This callback is intended to allow the application to inform the user of an upcoming delay. If failover is successful, a second call to the callback function occurs when the connection is reestablished and usable.

At this time the client may wish to replay `ALTER` `SESSION` commands and inform the user that failover has happened. If failover is unsuccessful, then the callback is called to inform the application that failover will not take place. Additionally, the callback is called each time a user handle besides the primary handle is re-authenticated on the new connection. Since each user handle represents a server-side session, the client may wish to replay `ALTER` `SESSION` commands for that session.

An initial attempt at failover may not always successful. The OCI provides a mechanism for retrying failover after an unsuccessful attempt.

See Also: See "Handling OCI_FO_ERROR" for more information about this scenario

### Failover Callback Structure and Parameters

The basic structure of a user-defined application failover callback function is as follows:

    sb4 appfocallback_fn ( dvoid      * svchp,
                           dvoid      * envhp,
                           dvoid      * fo_ctx,
                           ub4        fo_type,
                           ub4        fo_event );

An example is provided in the section "Failover Callback Example" for the following parameters:

svchp

  The first parameter, `svchp`, is the service context handle. It is of type `dvoid *`.

envhp

  The second parameter, `envhp`, is the OCI environment handle. It is of type `dvoid *`.

fo_ctx

  The third parameter, `fo_ctx`, is a client context. It is a pointer to memory specified by the client. In this area the client can keep any necessary state or context. It is passed as a `dvoid *`.

fo_type

  The fourth parameter, `fo_type`, is the failover type. This lets the callback know what type of failover the client has requested. The usual values are:

- `OCI_FO_SESSION`, which indicates that the user has requested only session failover.

- `OCI_FO_SELECT`, which indicates that the user has requested select failover as well.

fo_event

  The last parameter is the failover event. This indicates to the callback why it is being called. It has several possible values:

- `OCI_FO_BEGIN` indicates that failover has detected a lost connection and failover is starting.

- `OCI_FO_END` indicates successful completion of failover.

- `OCI_FO_ABORT` indicates that failover was unsuccessful, and there is no option of retrying.

- `OCI_FO_ERROR` also indicates that failover was unsuccessful, but it gives the application the opportunity to handle the error and retry failover.

- `OCI_FO_REAUTH` indicates that you have multiple authentication handles and failover has occurred after the original authentication. It indicates that a user handle has been re-authenticated. To find out which, the application checks the `OCI_ATTR_SESSION` attribute of the service context handle (which is the first parameter).

### Failover Callback Registration

For the failover callback to be used, it must be registered on the server context handle. This registration is done by creating a callback definition structure and setting the `OCI_ATTR_FOCBK` attribute of the server handle to this structure.

The callback definition structure must be of type `OCIFocbkStruct`. It has two fields: `callback_function`, which contains the address of the function to call, and `fo_ctx` which contains the address of the client context.

An example of callback registration is included as part of the example in the next section.

### Failover Callback Example

The following code shows an example of a simple user-defined callback function definition, registration, and unregistration.

#### Part 1: Failover Callback Definition

    sb4  callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event)
    dvoid * svchp;
    dvoid * envhp;
    dvoid *fo_ctx;
    ub4 fo_type;
    ub4 fo_event;
    {
    switch (fo_event)
       {
       case OCI_FO_BEGIN:
       {
         printf(" Failing Over ... Please stand by \n");
         printf(" Failover type was found to be %s \n",
                         ((fo_type==OCI_FO_SESSION) ? "SESSION"
                         :(fo_type==OCI_FO_SELECT) ? "SELECT"
                         : "UNKNOWN!"));
         printf(" Failover Context is :%s\n",
                        (fo_ctx?(char *)fo_ctx:"NULL POINTER!"));
         break;
       }
       case OCI_FO_ABORT:
       {
         printf(" Failover stopped. Failover will not take place.\n");
         break;
       }
       case    OCI_FO_END:
       {
           printf(" Failover ended ...resuming services\n");
         break;
       }
       case OCI_FO_REAUTH:
       {
           printf(" Failed over user. Resuming services\n");
         break;
       }
       default:
       {
         printf("Bad Failover Event: %d.\n",  fo_event);
         break;
       }
       }
       return 0;
    }

#### Part 2: Failover Callback Registration

    int register_callback(srvh, errh)
    dvoid *srvh; /* the server handle */
    OCIError *errh; /* the error handle */
    {
      OCIFocbkStruct failover;                 /*  failover callback structure */
      /* allocate memory for context */
      if (!(failover.fo_ctx = (dvoid *)malloc(strlen("my context.")+1)))
         return(1);
      /* initialize the context. */
      strcpy((char *)failover.fo_ctx, "my context.");
      failover.callback_function = &callback_fn;
      /* do the registration */
      if (OCIAttrSet(srvh, (ub4) OCI_HTYPE_SERVER,
                    (dvoid *) &failover, (ub4) 0,
                    (ub4) OCI_ATTR_FOCBK, errh)  != OCI_SUCCESS)
         return(2);
      /* successful conclusion */
      return (0);
    }

#### Part 3: Failover Callback Unregistration

    OCIFocbkStruct failover;   /*  failover callback structure */
    sword status;

      /* set the failover context to null */
      failover.fo_ctx = NULL;
      /* set the failover callback to null */
      failover.callback_function = NULL;
      /* un-register the callback */
      status = OCIAttrSet(srvhp, (ub4) OCI_HTYPE_SERVER,
                          (dvoid *) &failover, (ub4) 0,
                          (ub4) OCI_ATTR_FOCBK, errhp);

### Handling OCI_FO_ERROR

A failover attempt is not always successful. If the attempt fails, the callback function receives a value of `OCI_FO_ABORT` or `OCI_FO_ERROR` in the `fo_event` parameter. A value of `OCI_FO_ABORT` indicates that failover was unsuccessful, and no further failover attempts are possible. `OCI_FO_ERROR`, on the other hand, provides the callback function with the opportunity to handle the error in some way. For example, the callback may choose to wait a specified period of time and then indicate to the OCI library that it must reattempt failover.

Note: This functionality is only available to applications linked with the 8.0.5 or later OCI libraries running against any Oracle server. Failover does not work if a LOB column is part of the select list.

Consider the following timeline of events:

Table 9-4 Time and Event

Time | Event
--- | ---
T0 | Database fails (failure lasts until T5).
T1 | Failover triggered by user activity.
T2 | User attempts to reconnect; attempt fails.
T3 | Failover callback invoked with `OCI_FO_ERROR`.
T4 | Failover callback enters predetermined sleep period.
T5 | Database comes back up again.
T6 | Failover callback triggers new failover attempt; it is successful.
T7 | User successfully reconnects

The callback function triggers the new failover attempt by returning a value of `OCI_FO_RETRY` from the function.

The following example code shows a callback function which can be used to implement the failover strategy similar to the scenario described earlier. In this case the failover callback enters a loop in which it sleeps and then reattempts failover until it is successful:

    /*--------------------------------------------------------------------*/
    /* the user defined failover callback  */
    /*--------------------------------------------------------------------*/
    sb4  callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event )
    dvoid * svchp;
    dvoid * envhp;
    dvoid *fo_ctx;
    ub4 fo_type;
    ub4 fo_event;
    {
       OCIError *errhp;
       OCIHandleAlloc(envhp, (dvoid **)&errhp, (ub4) OCI_HTYPE_ERROR,
                  (size_t) 0, (dvoid **) 0);
       switch (fo_event)
       {
       case OCI_FO_BEGIN:
       {
         printf(" Failing Over ... Please stand by \n");
         printf(" Failover type was found to be %s \n",
                ((fo_type==OCI_FO_NONE) ? "NONE"
                 :(fo_type==OCI_FO_SESSION) ? "SESSION"
                 :(fo_type==OCI_FO_SELECT) ? "SELECT"
                 :(fo_type==OCI_FO_TXNAL) ? "TRANSACTION"
                 : "UNKNOWN!"));
         printf(" Failover Context is :%s\n",
                (fo_ctx?(char *)fo_ctx:"NULL POINTER!"));
         break;
       }
       case OCI_FO_ABORT:
       {
         printf(" Failover aborted. Failover will not take place.\n");
         break;
       }
       case    OCI_FO_END:
       {
           printf("\n Failover ended ...resuming services\n");
         break;
       }
       case OCI_FO_REAUTH:
       {
           printf(" Failed over user. Resuming services\n");
         break;
       }
       case OCI_FO_ERROR:
       {
         /* all invocations of this can only generate one line. The newline
          * will be put at fo_end time.
          */
         printf(" Failover error gotten. Sleeping...");
         sleep(3);
         printf("Retrying. ");
         return (OCI_FO_RETRY);
         break;
       }
       default:
       {
         printf("Bad Failover Event: %d.\n",  fo_event);
         break;
       }
       }
       return 0;
    }

## HA Event Notification

Suppose an user employs a Web browser to log in to an application server that accesses a back-end database server. Failure of the database instance can result in a wait that can be up to in minutes in duration before the failure is known to the user. The ability to quickly detect failures of server instances, communicate this to the client, close connections, and clean up idle connections in connection pools is provided by HA event notification.

For High Availability clients connected to a Real Application Clusters (RAC) database, HA event notification can be used to provide a best-effort programmatic signal to the client in the event of a database failure. Client applications can register a callback on the environment handle to signal interest in this information. When a significant failure event occurs (which applies to a connection made by this client), the callback is invoked, with information concerning the event (the event payload), and a list of connections (server handles) that were disconnected as a result of the failure.

For example, consider a client application that has two connections to instance A and two connections to instance B of the same database. If instance A goes down, a notification of the event will be sent to the client, which will then disconnect the two connections to instance B, and invoke the registered callback. Note that if another instance, C, of the same database, goes down, the client will not be notified (since it will not affect any of the client's connections).

The HA event notification mechanism improves the response time of the application in the presence of failure. In the past, a failure would result in the connection being broken only after the TCP time out expired, which could take minutes. With HA event notification, standalone, connection pool, and session pool connections are automatically broken and cleaned up by OCI and the application callback is invoked within seconds of the failure event. If any of these server handles are TAF-enabled, failover will also automatically be engaged by OCI.

Applications must connect to a RAC instance to enable HA event notification. Furthermore, these applications must:

- Initialize the OCI Environment in `OCI_EVENTS` mode.

- Connect to a service that has notifications enabled (use the `DBMS_SERVICE.MODIFY_SERVICE` procedure to set `AQ_HA_NOTIFICATIONS` to `TRUE`).

- Link with a thread library.

Then these applications can register a callback that is invoked whenever an HA event occurs.

### OCIEvent Handle

The `OCIEvent` handle encapsulates the attributes from the event payload. OCI implicitly allocates this handle prior to calling the event callback, which can obtain the read-only attributes of the event by calling `OCIAttrGet()`. Memory associated with these attributes is only valid for the duration of the event callback.

See Also: "Event Handle Attributes"

### OCI Failover for Connection and Session Pools

A connection pool in an instance of Real Application Clusters (RAC) consists of a pool of connections connected to different instances of the RAC. Upon receiving the node failure notification, all the connections connected to that particular instance should be cleaned up. For the connections that are in use, OCI has to close the connections: transparent application failover (TAF) occurs immediately and those connections will be reestablished. The connections that are idle and in the free list of the pool have to be purged, so that a bad connection is never returned back to the user from the pool.

To accommodate custom connection pools, OCI will provide a callback function that can be registered on the environment handle. If registered, this callback is invoked when an HA event occurs. Sessions pools are treated the same way as connection pools. Note that server handles from OCI connection pools or session pools will not be passed to the callback. Hence in some cases, the callback could be called with an empty list of connections.

### OCI Failover for Independent Connections

No special handling is required for independent connections; all such connections that are connected to failed instances are immediately disconnected. For idle connections, TAF will be engaged to reestablish the connection when the connection is used on a subsequent OCI call. Connections that are in use at the time of the failure event will be broken out immediately, so that TAF can begin. Note that this applies for the "in-use" connections of connection and session pools also.

### Event Callback

The event callback, of type `OCIEventCallback`, has the following signature:

    void evtcallback_fn (dvoid     *evtctx,
                         OCIEvent  *eventhp );

where `evtctx` is the client context and `OCIEvent` is an event handle which is opaque to the OCI library. The other input argument is `eventhp`, the event handle: that is, the attributes associated with an event.

If registered, this function will be called once for each event. In the case of RAC HA events, this callback will be invoked after the affected connections have been disconnected. The following environment handle attributes are used to register an event callback and context, respectively:

- `OCI_ATTR_EVTCBK` is of datatype `OCIEventCallback` `*`. It is read-only.

- `OCI_ATTR_EVTCTX` is of datatype `dvoid` `*`. It is also read-only.

    text *myctx = "dummy context"; /* dummy context passed to callback fn */
    ...
    /* OCI_ATTR_EVTCBK and OCI_ATTR_EVTCTX are read-only. */
    OCIAttrSet(envhp, (ub4) OCI_HTYPE_ENV, (dvoid *) evtcallback_fn,
               (ub4) 0, (ub4) OCI_ATTR_EVTCBK, errhp);
    OCIAttrSet(envhp, (ub4) OCI_HTYPE_ENV, (dvoid *) myctx,
               (ub4) 0, (ub4) OCI_ATTR_EVTCTX, errhp);
    ...

Within the OCI Event callback, the list of affected server handles is encapsulated in the `OCIEvent` handle. For RAC HA DOWN events, client applications can iterate over a list of server handles that are affected by the event by using `OCIAttrGet()` with attribute types `OCI_ATTR_HA_SVRFIRST` and `OCI_ATTR_HA_SVRNEXT`:

    OCIAttrGet(eventhp, OCI_HTYPE_EVENT, (dvoid *)&srvhp, (ub4 *)0,
               OCI_ATTR_HA_SRVFIRST, errhp);
    /* or, */
    OCIAttrGet(eventhp, OCI_HTYPE_EVENT, (dvoid *)&srvhp, (ub4 *)0,
               OCI_ATTR_HA_SRVNEXT, errhp);

When called with attribute `OCI_ATTR_HA_SRVFIRST`, this function will retrieve the first server handle in the list of server handles affected. When called with attribute `OCI_ATTR_HA_SRVNEXT`, this function will retrieve the next server handle in the list. This function will return `OCI_NO_DATA` and `srvhp` will be a `NULL` pointer, when there are no more server handles to return.

`srvhp` is an output pointer to a server handle whose connection has been closed as a result of an HA event. `errhp` is an error handle to populate. The application returns an `OCI_NO_DATA` error when there are no more affected server handles to retrieve.

When retrieving the list of server handles that have been affected by an HA event, be aware that the connection has already been closed and many server handle attributes are no longer valid. Instead, use the user memory segment of the server handle to store any per-connection attributes required by the event notification callback. This memory remains valid until the server handle is freed.

### Custom Pooling: Tagged Server Handles

The following features apply to custom pools:

1. You can tag a server handle with its parent connection object if it is created on behalf of a custom pool. Use the "user memory" parameters of `OCIHandleAlloc()` to request that the server handle be allocated with a user memory segment. A pointer to the "user memory" segment is returned by `OCIHandleAlloc()`.

2. When an HA event occurs, and an affected server handle has been retrieved, there is a means to retrieve the server handle's tag information so appropriate cleanup can be performed. The attribute `OCI_ATTR_USER_MEMORY` is used to retrieve a pointer to a handle's user memory segment. `OCI_ATTR_USER_MEMORY` is valid for all user-allocated handles. If the handle was allocated with extra memory, this attribute will return a pointer to the user memory. A `NULL` pointer will be returned for those handles not allocated with extra memory. This attribute is read-only and is of datatype `dvoid*`.

Note: You are free to define the precise contents of the server handle's user memory segment to facilitate cleanup activities from within the HA event callback (or for other purposes if needed) because OCI does not write or read from this memory in any way. The user memory segment is freed along with the `OCIHandleFree()` call on the server handle.

### Event Notification Example

    sword retval;
    OCIServer *srvhp;
    struct myctx {
       dvoid *parentConn_myctx;
       uword numval_myctx;
    };
    typedef struct myctx myctx;
    myctx  *myctxp;
    /* Allocate a server handle with user memory - pre 10.2 functionality */
    if (retval = OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER,
                                (size_t)sizeof(myctx), (dvoid **)&myctxp)
    /* handle error */
    myctxp->parentConn_myctx = <parent connection reference>;

    /* In an event callback function, retrieve the pointer to the user memory */
    evtcallback_fn(dvoid *evtctx, OCIEvent *eventhp)
    {
      myctx *ctxp = (myctx *)evtctx;
      OCIServer *srvhp;
      OCIError *errhp;
      sb4       retcode;
      retcode = OCIAttrGet(eventhp, OCI_HTYPE_SERVER, &srvhp, (ub4 *)0,
                           OCI_ATTR_HA_SRVFIRST, errhp);
      while (!retcode) /* OCIAttrGet will return OCI_NO_DATA if no more srvhp */
      {
         OCIAttrGet((dvoid *)srvhp, OCI_HTYPE_SERVER, (dvoid *)&ctxp,
                    (ub4)0, (ub4)OCI_ATTR_USER_MEMORY, errhp);
               /* Remove the server handle from the parent connection object */
         retcode = OCIAttrGet(eventhp, OCI_HTYPE_SERVER, &srvhp, (ub4 *)0,
                              OCI_ATTR_HA_SRVNEXT, errhp);
    ...
      }
    ...
    }

### Determining Transparent Application Failover (TAF) Capabilities

You can have the application adjust its behavior if a connection is or is not TAF-enabled. Use `OCIAttrGet()` as follows to find out if a server handle is TAF-enabled:

    boolean taf_capable;
    ...
    OCIAttrGet(srvhp, (ub4) OCI_HTYPE_SERVER, (dvoid *) &taf_capable,
               (ub4) sizeof(taf_capable), (ub4)OCI_ATTR_TAF_ENABLED, errhp);
    ...

where `taf_capable` is a boolean variable, which this call sets to `TRUE` if the server handle is TAF-enabled, and `FALSE` if not; `srvhp` is an input target server handle; `OCI_ATTR_TAF_ENABLED` is a an attribute which is a pointer to a boolean variable and is read-only; `errhp` is an input error handle.

## OCI and Streams Advanced Queuing

The OCI provides an interface to Streams Advanced Queuing (Streams AQ) feature. Streams AQ provides message queuing as an integrated part of the Oracle server. Streams AQ provides this functionality by integrating the queuing system with the database, thereby creating a message-enabled database. By providing an integrated solution Streams AQ frees application developers to devote their efforts to their specific business logic rather than having to construct a messaging infrastructure.

Note: In order to use Streams Advanced Queuing, you must be using the Enterprise Edition

See Also: Oracle Streams Advanced Queuing User's Guide and Reference Oracle XML Developer's Kit Programmer's Guide Oracle Integration Server Overview For example code demonstrating the use of the OCI with AQ, refer to the description of OCIAQEnq()

### OCI Streams Advanced Queuing Functions

The OCI library includes several functions related to Streams Advanced Queuing:

- `OCIAQEnq()`

- `OCIAQDeq()`

- `OCIAQListen()`

- `OCIAQListen2()`

- `OCIAQEnqArray()`

- `OCIAQDeqArray()`

You can enqueue an array of messages to a single queue. The messages all share the same enqueue options, but each message in the array can have different message properties. You can also dequeue an array of messages from a single queue. For transaction group queues, you can dequeue all messages for a single transaction group using one call.

See Also: "Streams Advanced Queuing and Publish-Subscribe Functions"

### OCI Streams Advanced Queuing Descriptors

The following descriptors are used by OCI Streams AQ operations:

- `OCIAQEnqOptions`

- `OCIAQDeqOptions`

- `OCIAQMsgProperties`

- `OCIAQAgent`

You can allocate these descriptors with respect to the service handle using the standard `OCIDescriptorAlloc()` call. The following code shows examples of this:

    OCIDescriptorAlloc(svch, &enqueue_options, OCI_DTYPE_AQENQ_OPTIONS, 0, 0 );
    OCIDescriptorAlloc(svch, &dequeue_options, OCI_DTYPE_AQDEQ_OPTIONS, 0, 0 );
    OCIDescriptorAlloc(svch, &message_properties, OCI_DTYPE_AQMSG_PROPERTIES, 0, 0);
    OCIDescriptorAlloc(svch, &agent, OCI_DTYPE_AQAGENT, 0, 0 );

Each descriptor has a variety of attributes which can be set or read.

See Also: "Streams Advanced Queuing Descriptor Attributes"

### Streams Advanced Queuing in OCI Versus PL/SQL

The following tables compare functions, parameters, and options for OCI Streams AQ functions and descriptors, and PL/SQL AQ functions in the DBMS_AQ package.

Table 9-5 AQ Functions

PL/SQL Function | OCI Function
--- | ---
DBMS_AQ.ENQUEUE | `OCIAQEnq()`
DBMS_AQ.DEQUEUE | `OCIAQDeq()`
DBMS_AQ.LISTEN | `OCIAQListen(), OCIAQListen2()`
DBMS_AQ.ENQUEUE_ARRAY | `OCIAQEnqArray()`
DBMS_AQ.DEQUEUE_ARRAY | `OCIAQDeqArray()`

The following table compares the parameters for the enqueue functions:

Table 9-6 Enqueue Parameters

DBMS_AQ.ENQUEUE Parameter | OCIAQEnq() Parameter
--- | ---
    queue_name
    queue_name
    enqueue_options
    enqueue_options
    message_properties
    message_properties
    payload
    payload
    msgid
    msgid
- | Note: `OCIAQEnq()` also requires the following additional parameters: `svch``,` `errh, payload_tdo, payload_ind``, and` `flags`.

The following table compares the parameters for the dequeue functions:

Table 9-7 Dequeue Parameters

DBMS_AQ.DEQUEUE Parameter | OCIAQDeq() Parameter
--- | ---
    queue_name
    queue_name
    dequeue_options
    dequeue_options
    message_properties
    message_properties
    payload
    payload
    msgid
    msgid
- | Note: `OCIAQDeq()` also requires the following additional parameters: `svch``,` `errh, dequeue_options, message_properties, payload_tdo, payload, payload_ind``, and` `flags`.

The following table compares parameters for the listen functions:

Table 9-8 Listen Parameters

DBMS_AQ.LISTEN Parameter | OCIAQListen2() Parameter
--- | ---
    agent_list
    agent_list
    wait
    wait
    agent
    agent
listen_delivery_mode | lopts
- | Note: `OCIAQListen2()` also requires the following additional parameters: `svchp, errhp, agent_list, num_agents, agent``, lmops, and` `flags`.

The following table compares parameters for the array enqueue functions:

Table 9-9 Array Enqueue Parameters

DBMS_AQ.ENQUEUE_ARRAY Parameter | OCIAQEnqArray() Parameter
--- | ---
    queue_name
    queue_name
    enqueue_options
    enqopt
    array_size
    iters
    message_properties_array
    msgprop
    payload_array
    payload
    msgid_array
    msgid
- | Note: `OCIAQEnqArray()` also requires the following additional parameters: `svch``,` `errh, payload_tdo, payload_ind, ctxp, enqcbfp,` `and` `flags`.

The following table compares parameters for the array dequeue functions:

Table 9-10 Array Dequeue Parameters

DBMS_AQ.DEQUEUE_ARRAY Parameter | OCIAQDeqArray() Parameter
--- | ---
    queue_name
    queue_name
    dequeue_options
    deqopt
    array_size
    iters
    message_properties_array
    msgprop
    payload_array
    payload
    msgid_array
    msgid
- | Note: `OCIAQDeqArray()` also requires the following additional parameters: `svch``,` `errh,` `msgprop, payload_tdo, payload_ind, ctxp, deqcbfp,` `and` `flags`.

The following table compares parameters for the agent attributes:

Table 9-11 Agent Parameters

PL/SQL Agent Parameter | OCIAQAgent Attribute
--- | ---
    name
| `OCI_ATTR_AGENT_NAME`
    address
| `OCI_ATTR_AGENT_ADDRESS`
    protocol
| `OCI_ATTR_AGENT_PROTOCOL`

The following table compares parameters for the message properties:

Table 9-12 Message Properties

PL/SQL Message Property | OCIAQMsgProperties Attribute
--- | ---
    priority
| `OCI_ATTR_PRIORITY`
    delay
| `OCI_ATTR_DELAY`
    expiration
| `OCI_ATTR_EXPIRATION`
    correlation
| `OCI_ATTR_CORRELATION`
    attempts
| `OCI_ATTR_ATTEMPTS`
    recipient_list
| `OCI_ATTR_RECIPIENT_LIST`
    exception_queue
| `OCI_ATTR_EXCEPTION_QUEUE`
    enqueue_time
| `OCI_ATTR_ENQ_TIME`
    state
| `OCI_ATTR_MSG_STATE`
    sender_id
| `OCI_ATTR_SENDER_ID`
    transaction_group
| `OCI_ATTR_TRANSACTION_NO`
    original_msgid
| `OCI_ATTR_ORIGINAL_MSGID`
delivery_mode | `OCI_ATTR_MSG_DELIVERY_MODE`

The following table compares enqueue option attributes:

Table 9-13 Enqueue Option Attributes

PL/SQL Enqueue Option | OCIAQEnqOptions Attribute
--- | ---
    visibility
| `OCI_ATTR_VISIBILITY`
    relative_msgid
| `OCI_ATTR_RELATIVE_MSGID`
    sequence_deviation
| `OCI_ATTR_SEQUENCE_DEVIATION` (deprecated)

The following table compares dequeue option attributes:

Table 9-14 Dequeue Option Attributes

PL/SQL Dequeue Option | OCIAQDeqOptions Attribute
--- | ---
    consumer_name
| `OCI_ATTR_CONSUMER_NAME`
    dequeue_mode
| `OCI_ATTR_DEQ_MODE`
    navigation
| `OCI_ATTR_NAVIGATION`
    visibility
| `OCI_ATTR_VISIBILITY`
    wait
| `OCI_ATTR_WAIT`
    msgid
| `OCI_ATTR_DEQ_MSGID`
    correlation
| `OCI_ATTR_CORRELATION`
delivery_mode | `OCI_ATTR_MSG_DELIVERY_MODE`

Note: `OCIAQEnq()` returns the error ORA-25219 while specifying the enqueue option `OCI_ATTR_SEQUENCE` along with `OCI_ATTR_RELATIVE_MSGID.` This happens when enqueuing two messages. For the second message, enqueue options `OCI_ATTR_SEQUENCE` and `OCI_ATTR_RELATIVE_MSGID` is set to dequeue this message before the first one. It does not return an error if you do not specify the 'sequence' but, of course, the message is not dequeued before the relative message. `OCIAQEnq()` does not return an error if `OCI_ATTR_SEQUENCE` attribute is not set, but the message is not dequeued before the message with relative message Id.

### Buffered Messaging

Buffered messaging is a nonpersistent messaging capability within Streams AQ, that was first available in Oracle Database 10g Release 2. Buffered messages reside in shared memory, and can be lost in the event of instance failure. Unlike persistent messages, no redo is written to disk. Buffered message enqueue and dequeue is much faster than persistent message operations. Since shared memory is limited, buffered messages may have to be spilled to disk. Flow control may be enabled to prevent applications from flooding the shared memory when the message consumers are slow or have stopped for some reason. The following functions are used for buffered messaging:

See Also: "OCIAQEnq()" "OCIAQEnqArray()" "OCIAQDeq()" "OCIAQDeqArray()" "OCIAQListen2()"

#### Enqueue Buffered Messaging Example

    ...
    OCIAQMsgProperties  *msgprop;
    OCIAQEnqueueOptions *enqopt;
    message              msg;    /* message is an object type */
    null_message         nmsg;   /* message indicator */
    ...
    /* Allocate descriptors */
      OCIDescriptorAlloc(envhp, (dvoid **)&enqopt, OCI_DTYPE_AQENQ_OPTIONS, 0,
                         (dvoid **)0));

     OCIDescriptorAlloc(envhp, (dvoid **)&msgprop,OCI_DTYPE_AQMSG_PROPERTIES, 0,
                        (dvoid **)0));

    /* Set delivery mode to buffered */
     dlvm = OCI_MSG_BUFFERED;
     OCIAttrSet(enqopt,  OCI_DTYPE_AQENQ_OPTIONS, (dvoid *)&dlvm, sizeof(ub2),
                OCI_ATTR_MSG_DELIVERY_MODE, errhp);
    /* Set visibility to Immediate (visibility must always be immediate for buffered
       messages) */
    vis = OCI_ENQ_ON_COMMIT;

    OCIAttrSet(enqopt, OCI_DTYPE_AQENQ_OPTIONS,(dvoid *)&vis, sizeof(ub4),
               OCI_ATTR_VISIBILITY, errhp)

    /* Message was an object type created earlier, msg_tdo is its Type
       Descriptor Object */
    OCIAQEnq(svchp, errhp, "Test_Queue", enqopt, msgprop, msg_tdo, (dvoid **)&mesg,
             (dvoid **)&nmesg, (OCIRaw **)0, 0));
    ...

#### Array Enqueue Buffered Messaging Example

    ...
    #define NMESGS 10
    OCIAQMsgProperties  *msgprop[NMESGS];
    OCIAQEnqueueOptions *enqopt;
    message              msg[NMESGS];    /* message is an object type  */
    null_message         nmsg[NMESGS];   /* message indicator */
    ...
    iters = NMESGS;

    OCIDescriptorAlloc(envhp, (dvoid **)&enqopt, OCI_DTYPE_AQENQ_OPTIONS, 0,
                       (dvoid **)0));
    /* visibility must be on_commit */
    vis = OCI_ENQ_ON_COMMIT;
    OCIAttrSet(enqopt, OCI_DTYPE_AQENQ_OPTIONS,(dvoid *)&vis, sizeof(ub4),
               OCI_ATTR_VISIBILITY, errhp)
    /* delivery mode is buffered */
    dlvm = OCI_MSG_BUFFERED;
    OCIAttrSet(enqopt,  OCI_DTYPE_AQENQ_OPTIONS, (dvoid *)&dlvm, sizeof(ub2),
               OCI_ATTR_MSG_DELIVERY_MODE, errhp);
    for (i = 0; i < NMESGS; i++)
    {
    OCIDescriptorAlloc(envhp, (dvoid **)&msgprop[i] ,OCI_DTYPE_AQMSG_PROPERTIES, 0,
                       (dvoid **)0));
    /* initialize the message */
    ...
    }

    OCIAQEnqArray(svchp, errhp, "Test_Queue", enqopt,  &iters, msgprop, msg_tdo,
                  (dvoid **)&msg,(dvoid **)&nmsg, (OCIRaw **)0, (dvoid *)0,
                  (OCICallbackAQEnq)0, (ub4)0);
    ...

#### Dequeue Buffered Messaging Example

    ...
    OCIAQMsgProperties  *msgprop;
    OCIAQDequeueOptions *deqopt;
    ...
    OCIDescriptorAlloc(envhp, (dvoid **)&mprop, OCI_DTYPE_AQMSG_PROPERTIES, 0,
                       (dvoid **)0));
    OCIDescriptorAlloc(envhp, (dvoid **)&deqopt, OCI_DTYPE_AQDEQ_OPTIONS, 0,
                       (dvoid **)0);

    /* Set visibility to Immediate (visibility must always be immediate for buffered
       message operations) */
    vis = OCI_ENQ_ON_COMMIT;
    OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS,(dvoid *)&vis, sizeof(ub4),
               OCI_ATTR_VISIBILITY, errhp)
    /* delivery mode is buffered */
    dlvm  = OCI_MSG_BUFFERED;
    OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS, (dvoid *)&dlvm,  sizeof(ub2),
               OCI_ATTR_MSG_DELIVERY_MODE, errhp);
    /* set the consumer for which to dequeue the message (this needs to be specified
       regardless of the type of message being dequeued.
    */
    consumer = "FIRST_SUBSCRIBER";
    OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS, (dvoid *)consumer,
               (ub4)strlen((char*)consumer), OCI_ATTR_CONSUMER_NAME, errhp);
    /* Dequeue the message but don't return the payload (to simplify the code
       snippet)
    */
    OCIAQDeq(svchp, errhp,  "test_queue", deqopt, msgprop, msg_tdo, (dvoid **)0,
             (dvoid **)0, (OCIRaw**)0, 0);
    ...

#### Array Dequeue Buffered Messaging Example

    #define NMESGS 10
    OCIAQMsgProperties  *msgprop[NMESGS];
    OCIAQDequeueOptions *deqopt;
    ...

    OCIDescriptorAlloc(envhp, (dvoid **)&deqopt, OCI_DTYPE_AQDEQ_OPTIONS, 0,
                       (dvoid **)0);
    /* Set visibility to Immediate (visibility must always be immediate for buffered
       message operations) */
    vis = OCI_ENQ_ON_COMMIT;
    OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS,(dvoid *)&vis, sizeof(ub4),
               OCI_ATTR_VISIBILITY, errhp)
    /* delivery mode is buffered */
    dlvm  = OCI_MSG_BUFFERED;
    OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS, (dvoid *)&dlvm, sizeof(ub2),
               OCI_ATTR_MSG_DELIVERY_MODE, errhp);
    /* set the consumer for which to dequeue the message (this needs to be specified
       regardless of the type of message being dequeued.
    */
    consumer = "FIRST_SUBSCRIBER";
    OCIAttrSet(deqopt, OCI_DTYPE_AQDEQ_OPTIONS, (dvoid *)consumer,
               (ub4)strlen((char*)consumer), OCI_ATTR_CONSUMER_NAME, errhp);
    for (i = 0; i < NMESGS; i++)
    {
    OCIDescriptorAlloc(envhp, (dvoid **)&msgprop[i] ,OCI_DTYPE_AQMSG_PROPERTIES, 0,
                       (dvoid **)0));
    }
    /* don't return message payload (to simplify the code snippet */
    OCIAQDeqArray(svchp, errhp, "test_queue", (OCIAQDeqOptions *)deqopt,  &iters, 0,
                  mesg_tdo,  (dvoid **)0, (dvoid **)0, 0, 0, 0, 0));
    ...

## Publish-Subscribe Notification in OCI

The publish-subscribe notification feature allows an OCI application to receive client notifications directly, register an e-mail address to which notifications can be sent, register a HTTP URL to which notifications can be posted, or register a PL/SQL procedure to be invoked on a notification. Figure 9-2, "Publish-Subscribe Model" illustrates the process.

Figure 9-2 Publish-Subscribe Model

Description of "Figure 9-2 Publish-Subscribe Model"

An OCI application can:

- register interest in notifications in the AQ namespace and be notified when an enqueue occurs.

- register interest in subscriptions to database events and receive notifications when the events are triggered.

- manage registrations, such as disabling registrations temporarily or dropping the registrations entirely.

- post, or send, notifications to registered clients.

In all the preceding scenarios the notification can be received directly by the OCI application, or the notification can be sent to a pre-specified e-mail address, or it can be sent to a pre-defined HTTP URL, or a pre-specified database PL/SQL procedure can be invoked as a result of a notification.

Registered clients are notified asynchronously when events are triggered or on an explicit AQ enqueue. Clients do not need to be connected to a database.

See Also: For information on Streams Advanced Queuing, see "OCI and Streams Advanced Queuing" For information on creating queues and about Streams AQ, including concepts, features, and examples, refer to Oracle Streams Advanced Queuing User's Guide and Reference For information on creating triggers, refer to the chapter on Commands in the Oracle Database SQL Reference.

### Publish-Subscribe Registration Functions in OCI

Registration can be done in two ways:

- You register directly to the database. This way is simple and the registration will take effect immediately.

- Open Registration. You register using LDAP, from which the database receives the registration request. This is useful when the client cannot have a database connection (the client wants to register for a database open event while the database is down), or if the client wants to register for the same event or events in multiple databases at one time.

Let us next consider these two alternative ways of registration.

#### Publish-Subscribe Register Directly to the Database

The following steps are required in an OCI application to register and receive notifications for events. It is assumed that the appropriate event trigger or AQ queue has been set up. The initialization parameter `COMPATIBLE` must be set to 8.1 or higher.

See Also: Detailed descriptions of the functions noted can be found in"Streams Advanced Queuing and Publish-Subscribe Functions" For examples of the use of these functions in an application, see "Publish-Subscribe Direct Registration Example"

Note: The publish-subscribe feature is only available on multithreaded operating systems.

1. Call `OCIInitialize()` with `OCI_EVENTS` mode to specify that the application is interested in registering for and receiving notifications. This starts a dedicated listening thread for notifications on the client.

2. Call `OCIHandleAlloc()` with handle type `OCI_HTYPE_SUBSCRIPTION` to allocate a subscription handle.

3. Call `OCIAttrSet()` to set the subscription handle attributes for:

   - `OCI_ATTR_SUBSCR_NAME` - subscription name

   - `OCI_ATTR_SUBSCR_NAMESPACE` - subscription namespace

   - `OCI_ATTR_SUBSCR_CALLBACK` - notification callback

   - `OCI_ATTR_SUBSCR_CTX` - callback context

   - `OCI_ATTR_SUBSCR_PAYLOAD` - payload buffer for posting

   - `OCI_ATTR_SUBSCR_RECPT` - recipient name

   - `OCI_ATTR_SUBSCR_RECPTPROTO` - protocol to receive notification with

   - `OCI_ATTR_SUBSCR_RECPTPRES` - presentation to receive notification with

   - `OCI_ATTR_SUBSCR_QOSFLAGS` - QOS (quality of service) levels

   - `OCI_ATTR_SUBSCR_TIMEOUT` - Registration timeout interval in seconds. The default is 0 if a timeout is not set.

   `OCI_ATTR_SUBSCR_NAME`, `OCI_ATTR_SUBSCR_NAMESPACE` and `OCI_ATTR_SUBSCR_RECPTPROTO` must be set before registering a subscription.

   If `OCI_ATTR_SUBSCR_RECPTPROTO` is set to `OCI_SUBSCR_PROTO_OCI`, then `OCI_ATTR_SUBSCR_CALLBACK` and `OCI_ATTR_SUBSCR_CTX` also need to be set.

   If `OCI_ATTR_SUBSCR_RECPTPROTO` is set to `OCI_SUBSCR_PROTO_MAIL`, `OCI_SUBSCR_PROTO_SERVER`, or `OCI_SUBSCR_PROTO_HTTP`, then `OCI_ATTR_SUBSCR_RECPT` also needs to be set.

   Setting `OCI_ATTR_SUBSCR_CALLBACK` and `OCI_ATTR_SUBSCR_RECPT` at the same time will cause an application error.

   `OCI_ATTR_SUBSCR_PAYLOAD` is required before posting to a subscription.

   See Also: "Subscription Handle Attributes"

4. The values of QOS, timeout interval, namespace, and port are set:

   See Also: "Setting QOS, Timeout Interval, Namespace, and Port Number"

5. If `OCI_ATTR_SUBSCR_RECPTPROTO` is set to `OCI_SUBSCR_PROTO_OCI`, then define the callback routine to be used with the subscription handle.

   See Also: "Notification Callback in OCI"

6. If `OCI_ATTR_SUBSCR_RECPTPROTO` is set to `OCI_SUBSCR_PROTO_SERVER`, then define the PL/SQL procedure, to be invoked on notification, in the database.

   See Also: "Notification Procedure"

7. Call `OCISubscriptionRegister()` to register with the subscriptions. This call can register interest in several subscriptions at the same time.

#### Open Registration for Publish-Subscribe

Prerequisites for this method are:

- Registering using LDAP (open registration) requires the client to be an enterprise user.

  See Also: Oracle Database Advanced Security Administrator's Guide, sections on managing enterprise user security

- The compatibility of the database has to be 9.0 or higher.

- LDAP_REGISTRATION_ENABLED must be set to TRUE. This can be done this way:

      ALTER SYSTEM SET LDAP_REGISTRATION_ENABLED=TRUE

  The default is FALSE.

- LDAP_REG_SYNC_INTERVAL must be set to the time interval (in seconds) to refresh registrations from LDAP:

      ALTER SYSTEM SET LDAP_REG_SYNC_INTERVAL =  time_interval

  The default is 0, which means do not refresh.

- To force a database refresh of LDAP registration information immediately:

      ALTER SYSTEM REFRESH LDAP_REGISTRATION

The steps in open registration using Oracle Enterprise Security Manager (OESM) are:

1. In each enterprise domain, create enterprise role, ENTERPRISE_AQ_USER_ROLE.

2. For each database in the enterprise domain, add global role GLOBAL_AQ_USER_ROLE to enterprise role ENTERPRISE_AQ_USER_ROLE.

3. For each enterprise domain, add enterprise role ENTERPRISE_AQ_USER_ROLE to privilege group cn=OracleDBAQUsers, under cn=oraclecontext, under the administrative context.

4. For each enterprise user that is authorized to register for events in the database, grant enterprise role ENTERPRISE_AQ_USER_ROLE.

#### Using OCI to Open Register with LDAP

1. Call `OCIInitialize()` with `mode` set to `OCI_EVENTS` | `OCI_USE_LDAP`.

2. Call `OCIAttrSet()` to set the following environment handle attributes for accessing LDAP:

   - `OCI_ATTR_LDAP_HOST`: the host name on which the LDAP server resides

   - `OCI_ATTR_LDAP_PORT`: the port on which the LDAP server is listening

   - `OCI_ATTR_BIND_DN`: the distinguished name to login to the LDAP server, usually the DN of the enterprise user

   - `OCI_ATTR_LDAP_CRED`: the credential used to authenticate the client, for example, the password for simple authentication (user name and password)

   - `OCI_ATTR_WALL_LOC`: for SSL authentication, the location of the client wallet

   - `OCI_ATTR_LDAP_AUTH`: the authentication method code

     See Also: "Environment Handle Attributes" for a complete list of authentication modes

   - `OCI_ATTR_LDAP_CTX`: the administrative context for Oracle in the LDAP server

3. Call `OCIHandleAlloc()` with handle type `OCI_HTYPE_SUBSCRIPTION`, to allocate a subscription handle.

4. Call `OCIDescriptorAlloc()` with descriptor type `OCI_DTYPE_SRVDN`, to allocate a server DN descriptor.

5. Call `OCIAttrSet()` to set the server DN descriptor attributes for `OCI_ATTR_SERVER_DN`, the distinguished name of the database in which the client wants to receive notifications. `OCIAttrSet()` can be called multiple times for this attribute so that more than one database server is included in the registration

6. Call `OCIAttrSet()` to set the subscription handle attributes for:

   - `OCI_ATTR_SUBSCR_NAME`: subscription name

   - `OCI_ATTR_SUBSCR_NAMESPACE`: subscription namespace

   - `OCI_ATTR_SUBSCR_CALLBACK`: notification callback

   - `OCI_ATTR_SUBSCR_CTX`: callback context

   - `OCI_ATTR_SUBSCR_PAYLOAD`: payload buffer for posting

   - `OCI_ATTR_SUBSCR_RECPT`: recipient name

   - `OCI_ATTR_SUBSCR_RECPTPROTO`: protocol to receive notification

   - `OCI_ATTR_SUBSCR_RECPTRES`: presentation to receive notification with

   - `OCI_ATTR_SUBSCR_QOSFLAGS` - QOS (quality of service) levels

   - `OCI_ATTR_SUBSCR_TIMEOUT` - Registration timeout interval in seconds. The default is 0 if a timeout is not set.

   - `OCI_ATTR_SUBSCR_SERVER_DN`: the descriptor handles you populated in step 5

7. The values of QOS, timeout interval, namespace, and port are set:

   See Also: "Setting QOS, Timeout Interval, Namespace, and Port Number"

8. Call `OCISubscriptionRegister()` to register the subscriptions. The registration will take effect when the database accesses LDAP to pick up new registrations. The frequency of pick-ups is determined by the value of REG_SYNC_INTERVAL.

#### Setting QOS, Timeout Interval, Namespace, and Port Number

You can set QOSFLAGS to the following QOS levels using `OCIAttrSet()`:

- `OCI_SUBSCR_QOS_RELIABLE` - Reliable notification persists across instance and database restarts. Reliability is of the server only and is only for persistent queues or buffered messages. This option describes the persistence of the notifications. Registrations are persistent by default.

- `OCI_SUBSCR_QOS_PURGE_ON_NTFN` - Once received, purge registration on first notification. (Subscription is unregistered.)

    /* Set QOS levels */
    ub4 qosflags = OCI_SUBSCR_QOS_RELIABLE | OCI_SUBSCR_QOS_PURGE_ON_NTFN;

    /* Set flags in subscription handle */
    (void)OCIAttrSet((dvoid *)subscrhp, (ub4)OCI_HTYPE_SUBSCRIPTION,
                   (dvoid *)&qosflags, (ub4)0, (ub4)OCI_ATTR_SUBSCR_QOSFLAGS, errhp);

    /* Set auto-expiration after 30 seconds */
    ub4 timeout = 30;
    (void)OCIAttrSet((dvoid *)subscrhp, (ub4)OCI_HTYPE_SUBSCRIPTION,
                     (dvoid *)&timeout, (ub4)0, (ub4)OCI_ATTR_SUBSCR_TIMEOUT, errhp);

The registration is purged when the timeout is exceeded, and a notification is sent to the client, so that the client can invoke its callback and take any necessary action. In the case of client failure before the timeout, the registration is purged.

You can set the port number on the environment handle, which is important if the client is on a system behind a firewall that is able to receive notifications only on certain ports. Clients can specify the port for the listener thread before the first registration, using an attribute in the environment handle. The thread is started the first time `OCISubscriptionRegister()` is called. If available, this specified port number will be used. An error is returned if the client tries to start another thread on a different port using a different environment handle.

    ub4 port = 1581;
    (void)OCIAttrSet((dvoid *)envhp, (ub4)OCI_HTYPE_ENV, (dvoid *)&port, (ub4)0,
                     (ub4)OCI_ATTR_SUBSCR_PORTNO, errhp);

If instead, the port is determined automatically, you can get the port number at which the client thread is listening for notification by obtaining the attribute from the environment handle.

    (void)OCIAttrGet((dvoid *)subhp, (ub4)OCI_HTYPE_ENV, (dvoid *)&port,
                     (ub4)0, (ub4)OCI_ATTR_SUBSCR_PORTNO, errhp);

#### OCI Functions Used to Manage Publish-Subscribe Notification

The following functions are used to manage publish-subscribe notification.

Table 9-15 Publish-Subscribe Functions

Function | Purpose
--- | ---
OCISubscriptionDisable() | Disables a subscription.
OCISubscriptionEnable() | Enables a subscription.
OCISubscriptionPost() | Posts a subscription.
OCISubscriptionRegister() | Registers a subscription.
OCISubscriptionUnRegister() | Unregisters a subscription.

### Notification Callback in OCI

The client needs to register a notification callback that gets invoked when there is some activity on the subscription for which interest has been registered. In the AQ namespace, for instance, this occurs when a message of interest is enqueued.

This callback is typically set through the `OCI_ATTR_SUBSCR_CALLBACK` attribute of the subscription handle.

See Also: "Subscription Handle Attributes"

The callback must return a value of `OCI_CONTINUE` and adhere to the following specification:

    typedef ub4 (*OCISubscriptionNotify) ( dvoid           *pCtx,
                                           OCISubscription *pSubscrHp,
                                           dvoid           *pPayload,
                                           ub4             iPayloadLen,
                                           dvoid           *pDescriptor,
                                           ub4             iMode);

The parameters are described as follows:

pCtx (IN)

  A user-defined context specified when the callback was registered.

pSubscrHp (IN)

  The subscription handle specified when the callback was registered.

pPayload (IN)

  The payload for this notification. For this release, only ub1 * (a sequence of bytes) for the payload is supported.

iPayloadLen (IN)

  The length of the payload for this notification.

pDescriptor (IN)

  The namespace-specific descriptor. Namespace-specific parameters can be extracted from this descriptor. The structure of this descriptor is opaque to the user and its type is dependent on the namespace.

  The attributes of the descriptor are namespace-specific. For Advanced Queuing, the descriptor is `OCI_DTYPE_AQNFY`. The attributes of this descriptor are:

- Queue Name - `OCI_ATTR_QUEUE_NAME`

- Consumer Name - `OCI_ATTR_CONSUMER_NAME`

- Message Id - `OCI_ATTR_NFY_MSGID`

- Message Properties - `OCI_ATTR_MSG_PROP`

  See Also: "OCI and Streams Advanced Queuing"

iMode (IN)

  Call-specific mode. Valid value:

- `OCI_DEFAULT` - executes the default call

### Notification Procedure

The PL/SQL procedure that will be invoked when there is some activity on the subscription for which interest has been registered, has to be created in the database.

This procedure is typically set through the `OCI_ATTR_SUBSCR_RECPT` attribute of the subscription handle.

See Also: See "Subscription Handle Attributes" . "Oracle Streams AQ PL/SQL Callback" in Oracle Database PL/SQL Packages and Types Reference for the PL/SQL procedure specification.

### Publish-Subscribe Direct Registration Example

This example shows how system events, client notification, and Advanced Queuing work together to implement publish subscription notification.

The following PL/SQL code creates all objects necessary to support a publish-subscribe mechanism under the user schema, `pubsub`. In this code, the Agent `snoop` subscribes to messages that are published at logon events. Note that the user `pubsub` needs AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE privileges to use Advance Queuing functionality. The initialization parameter _SYSTEM_TRIG_ENABLED must be set to TRUE (the default) to enable triggers for system events.

    ----------------------------------------------------------
    ----create queue table for persistent multiple consumers
    ----------------------------------------------------------
    connect pubsub/pubsub;
    ---- Create or replace a queue table
    begin
      DBMS_AQADM.CREATE_QUEUE_TABLE(
      QUEUE_TABLE=>'pubsub.raw_msg_table',
      MULTIPLE_CONSUMERS => TRUE,
      QUEUE_PAYLOAD_TYPE =>'RAW',
      COMPATIBLE => '8.1.5');
    end;
    /
    ----------------------------------------------------------
    ---- Create a persistent queue for publishing messages
    ----------------------------------------------------------
    ---- Create a queue for logon events
    begin
      DBMS_AQADM.CREATE_QUEUE(QUEUE_NAME=>'pubsub.logon',
      QUEUE_TABLE=>'pubsub.raw_msg_table',
      COMMENT=>'Q for error triggers');
    end;
    /
    ----------------------------------------------------------
    ---- Start the queue
    ----------------------------------------------------------
    begin
      DBMS_AQADM.START_QUEUE('pubsub.logon');
    end;
    /
    ----------------------------------------------------------
    ---- define new_enqueue for convenience
    ----------------------------------------------------------
    create or replace procedure new_enqueue(queue_name  in varchar2,
                                            payload  in raw ,
    correlation in varchar2 := NULL,
    exception_queue in varchar2 := NULL)
    as
      enq_ct     dbms_aq.enqueue_options_t;
      msg_prop   dbms_aq.message_properties_t;
      enq_msgid  raw(16);
      userdata   raw(1000);
    begin
      msg_prop.exception_queue := exception_queue;
      msg_prop.correlation := correlation;
      userdata := payload;
      DBMS_AQ.ENQUEUE(queue_name,enq_ct, msg_prop,userdata,enq_msgid);
    end;
    /
    ----------------------------------------------------------
    ---- add subscriber with rule based on current user name,
    ---- using correlation_id
    ----------------------------------------------------------
    declare
    subscriber sys.aq$_agent;
    begin
      subscriber := sys.aq$_agent('SNOOP', null, null);
      dbms_aqadm.add_subscriber(queue_name => 'pubsub.logon',
                                subscriber => subscriber,
                                rule => 'CORRID = ''ix'' ');
    end;
    /
    ----------------------------------------------------------
    ---- create a trigger on logon on database
    ----------------------------------------------------------
    ---- create trigger on after logon
    create or replace trigger systrig2
       AFTER LOGON
       ON DATABASE
       begin
         new_enqueue('pubsub.logon', hextoraw('9999'), dbms_standard.login_user);
       end;
    /

    ----------------------------------------------------------
    ---- create a PL/SQL callback for notification of logon
    ---- of user 'ix' on database
    ----------------------------------------------------------
    ----
    create or replace procedure plsqlnotifySnoop(
      context raw, reginfo sys.aq$_reg_info, descr sys.aq$_descriptor,
      payload raw, payloadl number)
    as
    begin
     dbms_output.put_line('Notification : User ix Logged on\n');
    end;
    /

After the subscriptions are created, the client needs to register for notification using callback functions. The following sample code performs the necessary steps for registration. The initial steps of allocating and initializing session handles are omitted here for sake of clarity.

    ...
    static ub4 namespace = OCI_SUBSCR_NAMESPACE_AQ;

    static OCISubscription *subscrhpSnoop = (OCISubscription *)0;
    static OCISubscription *subscrhpSnoopMail = (OCISubscription *)0;
    static OCISubscription *subscrhpSnoopServer = (OCISubscription *)0;

    /* callback function for notification of logon of user 'ix' on database */

    static ub4 notifySnoop(ctx, subscrhp, pay, payl, desc, mode)
        dvoid *ctx;
        OCISubscription *subscrhp;
        dvoid *pay;
        ub4 payl;
        dvoid *desc;
        ub4 mode;
    {
        printf("Notification : User ix Logged on\n");
      (void)OCIHandleFree((dvoid *)subscrhpSnoop,
                (ub4) OCI_HTYPE_SUBSCRIPTION);
        return 1;
    }

    static void checkerr(errhp, status)
    OCIError *errhp;
    sword status;
    {
      text errbuf[512];
      ub4 buflen;
      sb4 errcode;

      if (status == OCI_SUCCESS) return;

      switch (status)
      {
      case OCI_SUCCESS_WITH_INFO:
        printf("Error - OCI_SUCCESS_WITH_INFO\n");
        break;
      case OCI_NEED_DATA:
        printf("Error - OCI_NEED_DATA\n");
        break;
      case OCI_NO_DATA:
        printf("Error - OCI_NO_DATA\n");
        break;
      case OCI_ERROR:
        OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
        printf("Error - %s\n", errbuf);
        break;
      case OCI_INVALID_HANDLE:
        printf("Error - OCI_INVALID_HANDLE\n");
        break;
      case OCI_STILL_EXECUTING:
        printf("Error - OCI_STILL_EXECUTING\n");
        break;
      case OCI_CONTINUE:
        printf("Error - OCI_CONTINUE\n");
        break;
      default:
        printf("Error - %d\n", status);
        break;
      }
    }

    static void initSubscriptionHn (subscrhp,
                             subscriptionName,
                             func,
                             recpproto,
                             recpaddr,
                             recppres)
    OCISubscription **subscrhp;
      char * subscriptionName;
      dvoid * func;
      ub4 recpproto;
      char * recpaddr;
      ub4 recppres;
    {
        /* allocate subscription handle */
        (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)subscrhp,
            (ub4) OCI_HTYPE_SUBSCRIPTION,
            (size_t) 0, (dvoid **) 0);

        /* set subscription name in handle */
        (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
            (dvoid *) subscriptionName,
            (ub4) strlen((char *)subscriptionName),
            (ub4) OCI_ATTR_SUBSCR_NAME, errhp);

        /* set callback function in handle */
        if (func)
          (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
              (dvoid *) func, (ub4) 0,
              (ub4) OCI_ATTR_SUBSCR_CALLBACK, errhp);

        /* set context in handle */
        (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
            (dvoid *) 0, (ub4) 0,
           (ub4) OCI_ATTR_SUBSCR_CTX, errhp);

        /* set namespace in handle */
        (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
            (dvoid *) &namespace, (ub4) 0,
            (ub4) OCI_ATTR_SUBSCR_NAMESPACE, errhp);

        /* set receive with protocol in handle */
        (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
            (dvoid *) &recpproto, (ub4) 0,
            (ub4) OCI_ATTR_SUBSCR_RECPTPROTO, errhp);

        /* set recipient address in handle */
        if (recpaddr)
          (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
              (dvoid *) recpaddr, (ub4) strlen(recpaddr),
              (ub4) OCI_ATTR_SUBSCR_RECPT, errhp);

        /* set receive with presentation in handle */
        (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
            (dvoid *) &recppres, (ub4) 0,
            (ub4) OCI_ATTR_SUBSCR_RECPTPRES, errhp);

        printf("Begining Registration for subscription %s\n", subscriptionName);
        checkerr(errhp, OCISubscriptionRegister(svchp, subscrhp, 1, errhp,
            OCI_DEFAULT));
       printf("done\n");

    }

    int main( argc, argv)
    int    argc;
    char * argv[];
    {
        OCISession *authp = (OCISession *) 0;

    /*****************************************************
    Initialize OCI Process/Environment
    Initialize Server Contexts
    Connect to Server
    Set Service Context
    ******************************************************/

    /* Registration Code Begins */
    /* Each call to initSubscriptionHn allocates
       and Initialises a Registration Handle */

    /* Register for OCI notification */
        initSubscriptionHn(    &subscrhpSnoop,    /* subscription handle*/
        (char*) "PUBSUB.LOGON:SNOOP", /* subscription name */
                                      /*<queue_name>:<agent_name> */
            (dvoid*)notifySnoop,  /* callback function */
            OCI_SUBSCR_PROTO_OCI, /* receive with protocol */
            (char *)0, /* recipient address */
            OCI_SUBSCR_PRES_DEFAULT); /* receive with presentation */

    /* Register for email notification */
        initSubscriptionHn(    &subscrhpSnoopMail,  /* subscription handle */
         (char*) "PUBSUB.LOGON:SNOOP",              /* subscription name */
                                                    /* <queue_name>:<agent_name> */
            (dvoid*)0, /* callback function */
            OCI_SUBSCR_PROTO_MAIL, /* receive with protocol */
            (char*)  "longying.zhao@oracle.com", /* recipient address */
            OCI_SUBSCR_PRES_DEFAULT); /* receive with presentation */

    /* Register for server to server notification */
        initSubscriptionHn(    &subscrhpSnoopServer, /* subscription handle */
           (char*)  "PUBSUB.LOGON:SNOOP",            /* subscription name */
                                                     /* <queue_name>:<agent_name> */
            (dvoid*)0, /* callback function */
            OCI_SUBSCR_PROTO_SERVER, /* receive with protocol */
             (char*) "pubsub.plsqlnotifySnoop", /* recipient address */
            OCI_SUBSCR_PRES_DEFAULT); /* receive with presentation */

        checkerr(errhp, OCITransCommit(svchp, errhp, (ub4) OCI_DEFAULT));

    /*****************************************************
    The Client Process does not need a live Session for Callbacks
    End Session and Detach from Server
    ******************************************************/

        OCISessionEnd ( svchp,  errhp, authp, (ub4) OCI_DEFAULT);

        /* detach from server */
        OCIServerDetach( srvhp, errhp, OCI_DEFAULT);

       while (1)    /* wait for callback */
        sleep(1);
    }

If user `IX` logs on to the database, the client is notified by e-mail, and the callback function `notifySnoop` is called. An e-mail notification will be sent to the address xyz@company.com and the PL/SQL procedure `plsqlnotifySnoop` will also be called in the database.

### Publish-Subscribe LDAP Registration Example

The following code fragment illustrates how to do LDAP registration. Please read all the program comments:

    ...

      /* TO use LDAP registration feature, OCI_EVENTS | OCI_USE_LDAP must be set
         in OCIInitialize: */

      (void) OCIInitialize((ub4) OCI_EVENTS|OCI_OBJECT|OCI_USE_LDAP, (dvoid *)0,
                           (dvoid * (*)(dvoid *, size_t)) 0,
                           (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                           (void (*)(dvoid *, dvoid *)) 0 );

    ...

      /* set LDAP attributes in the environment handle */

      /* LDAP host name */
      (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)"yow", 3,
                        OCI_ATTR_LDAP_HOST, (OCIError *)errhp);

      /* LDAP server port */
      ldap_port = 389;
      (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&ldap_port,
                        (ub4)0, OCI_ATTR_LDAP_PORT, (OCIError *)errhp);

      /* bind DN of the client, normally the enterprise user name */
      (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)"cn=orcladmin",
                        12, OCI_ATTR_BIND_DN, (OCIError *)errhp);

      /* password of the client */
      (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)"welcome",
                        7, OCI_ATTR_LDAP_CRED, (OCIError *)errhp);

      /* authentication method is "simple", username/password authentication */
      ldap_auth = 0x01;
      (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&ldap_auth,
                        (ub4)0, OCI_ATTR_LDAP_AUTH, (OCIError *)errhp);

      /* adminstrative context: this is the DN above cn=oraclecontext */
      (void) OCIAttrSet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)"cn=acme,cn=com",
                        14, OCI_ATTR_LDAP_CTX, (OCIError *)errhp);

    ...

      /* retrieve the LDAP attributes from the environment handle */

      /* LDAP host */
      (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&buf,
                        &szp,  OCI_ATTR_LDAP_HOST,  (OCIError *)errhp);

      /* LDAP server port */
      (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&intval,
                        0,  OCI_ATTR_LDAP_PORT,  (OCIError *)errhp);

      /* client binding DN */
      (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&buf,
                        &szp,  OCI_ATTR_BIND_DN,  (OCIError *)errhp);

      /* client password */
      (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&buf,
                        &szp,  OCI_ATTR_LDAP_CRED,  (OCIError *)errhp);

      /* adminstrative context */
      (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&buf,
                        &szp,  OCI_ATTR_LDAP_CTX,  (OCIError *)errhp);

      /* client authentication method */
      (void) OCIAttrGet((dvoid *)envhp, OCI_HTYPE_ENV, (dvoid *)&intval,
                        0,  OCI_ATTR_LDAP_AUTH,  (OCIError *)errhp);

      ...

      /* to set up the server DN descriptor in the subscription handle */

      /* allocate a server DN descriptor, dn is of type "OCIServerDNs **",
         subhp is of type "OCISubscription **" */
      (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)dn,
                             (ub4) OCI_DTYPE_SRVDN, (size_t)0, (dvoid **)0);

      /* now *dn is the server DN descriptor, add the DN of the first database
         that we want to register */
      (void) OCIAttrSet((dvoid *)*dn, (ub4) OCI_DTYPE_SRVDN,
                        (dvoid *)"cn=server1,cn=oraclecontext,cn=acme,cn=com",
                        42, (ub4)OCI_ATTR_SERVER_DN, errhp);
      /* add the DN of another database in the descriptor */
      (void) OCIAttrSet((dvoid *)*dn, (ub4) OCI_DTYPE_SRVDN,
                        (dvoid *)"cn=server2,cn=oraclecontext,cn=acme,cn=com",
                        42, (ub4)OCI_ATTR_SERVER_DN, errhp);

      /* set the server DN descriptor into the subscription handle */
      (void) OCIAttrSet((dvoid *) *subhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                     (dvoid *) *dn, (ub4)0, (ub4) OCI_ATTR_SERVER_DNS, errhp);

      ...

      /* now we will try to get the server DN information from the subscription
         handle */

      /* first, get the server DN descriptor out */
      (void) OCIAttrGet((dvoid *) *subhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
                        (dvoid *)dn, &szp, OCI_ATTR_SERVER_DNS, errhp);

      /* then, get the number of server DNs in the descriptor */
      (void) OCIAttrGet((dvoid *) *dn, (ub4)OCI_DTYPE_SRVDN, (dvoid *)&intval,
                        &szp, (ub4)OCI_ATTR_DN_COUNT, errhp);

      /* allocate an array of char * to hold server DN pointers returned by
         oracle */
        if (intval)
        {
          arr = (char **)malloc(intval*sizeof(char *));
          (void) OCIAttrGet((dvoid *)*dn, (ub4)OCI_DTYPE_SRVDN, (dvoid *)arr,
                            &intval, (ub4)OCI_ATTR_SERVER_DN, errhp);
        }

      /* OCISubscriptionRegister() calls have two modes: OCI_DEFAULT and
         OCI_REG_LDAPONLY. If OCI_DEFAULT is used, there should be only one
         server DN in the server DN descriptor. The registration request will
         be sent to the database. If a database connection is not available,
         the registration request will be detoured to the LDAP server. On the
         other hand, if mode OCI_REG_LDAPONLY is used the registration request
         will be directly sent to LDAP. This mode should be used when there are
         more than one server DNs in the server DN descriptor, or we are sure
         that a database connection is not available.

         In this example, two DNs are entered; so we should use mode
         OCI_REG_LDAPONLY in register. */
      OCISubscriptionRegister(svchp, subhp, 1, errhp, OCI_REG_LDAPONLY);

      ...

      /* as OCISubscriptionRegister(), OCISubscriptionUnregister() also has
         mode OCI_DEFAULT and OCI_REG_LDAPONLY. The usage is the same. */

      OCISubscriptionUnRegister(svchp, *subhp, errhp, OCI_REG_LDAPONLY);
    }
    ...

## Database Change Notification

Database Change Notification is a feature that enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction commits.

During registration, the application specifies a notification handler and associates a set of interesting queries with the notification handler. A notification handler can be either a server side PL/SQL procedure or a client side C callback. Registrations are created on all objects referenced during the execution of the queries. The notification handler is invoked when a transaction subsequently changes any of the registered objects and commits.

See Also: Oracle Database Application Developer's Guide - Fundamentals, chapter 13, "Developing Applications with Database Change Notification"

One use of this feature is in middle-tier applications that need to have cached data and keep the cache as recent as possible with respect to the back-end database.

The contents of the notification includes the following information:

- Names of the modified objects

- Operation type (`INSERT`, `UPDATE`, `DELETE`, `ALTER` `TABLE`, `DROP` `TABLE`)

- ROWIDs of the changed rows and the associated operation (`INSERT`, `UPDATE`, `DELETE`)

- Global database events (`STARTUP`, `SHUTDOWN`)

### Registering for Database Change Notification

The calling session must have the `CHANGE` `NOTIFICATION` system privilege and `SELECT` privileges on all objects that it attempts to register. A registration is a persistent entity that is recorded in the database, and is visible to all instances of Real Application Clusters (RAC). Transactions that modify registered objects in any instance of the RAC generate notification.

The objects referenced are identified during the execution of the queries. If the query refers to a non-materialized view, then the underlying tables on top of which the view is based are registered. Queries based on materialized views are not supported.

The registration interface employs a callback to respond to changes in underlying objects of a query and uses a namespace extension to AQ, `DBCHANGE`.

The steps in writing the registration are:

- The environment must be created in `OCI_EVENTS` and `OCI_OBJECT` mode.

- The subscription handle attribute `OCI_ATTR_SUBSCR_NAMESPACE` must be set to namespace `OCI_SUBSCR_NAMESPACE_DBCHANGE`.

- The subscription handle attribute `OCI_ATTR_SUBSCR_CALLBACK` is used to store the OCI callback associated with the query handle. The callback has the following prototype:

      notification_callback (dvoid *ctx, OCISubscription *subscrhp, dvoid *payload,
                             ub4 paylen, dvoid *desc, ub4 mode)

  The parameters are described in "Notification Callback in OCI".

- You can optionally associate a client-specific context using `OCI_ATTR_SUBSCR_CTX`.

- `OCI_ATTR_SUBSCR_TIMEOUT` can be set to specify a ub4 timeout interval in seconds. If not set, there is no timeout.

- If `OCI_SUBSCR_QOS_PURGE_ON_NTFN` is set, the registration is purged on the first notification.

- If `OCI_SUBSCR_QOS_RELIABLE` is set, notifications are persistent. Surviving instances of a RAC can be used to send and retrieve change notification messages, even after a node failure because invalidations associated with this registration are queued persistently into the database. If `FALSE`, then invalidations are enqueued into a fast in-memory queue. Note that this option describes the persistence of notifications and not the persistence of registrations. Registrations are automatically persistent by default.

- Call `OCISubscriptionRegister()` to create a new registration in the `DBCHANGE` namespace.

- Multiple query statements can be associated with the subscription handle by setting the attribute `OCI_ATTR_CHNF_REGHANDLE` of the statement handle, `OCI_HTYPE_STMT`. The registration is completed when the query is executed.

- Optionally, to unregister a subscription, the client can call the `OCISubscriptionUnRegister()` function with the subscription handle as a parameter.

A binding of a statement handle to a subscription handle is only valid for the first execution of a query. If the application needs to use the same `OCIStatement` handle for subsequent executions, it must repopulate the registration handle attribute of the statement handle. A binding of a subscription handle to a statement handle is only permitted when the statement is a query (determined at execute time). If a DML statement is executed as part of the execution, then an exception is issued.

### Subscription Handle Attributes for Change Notification

The subscription handle attributes for change notification are described next. The attributes can be divided into generic (which are common to all subscriptions) and namespace- specific attributes particular to change notification. The IN attributes on the statement handle can only be modified before the registration is created.

`OCI_ATTR_SUBSCR_NAMESPACE` (generic) - This must be set to `OCI_SUBSCR_NAMESPACE_DBCHANGE` for subscription handles.

`OCI_ATTR_SUBSCR_CALLBACK` (generic) - Use to store the callback associated with the subscription handle. The callback is executed when a notification is received.

When a new change notification message becomes available, the callback is invoked in the listener thread with `desc` pointing to a descriptor of type `OCI_DTYPE_CHDES` which contains detailed information about the invalidation.

For setting `OCI_ATTR_SUBSCR_QOSFLAGS,` a generic flag, a parallel example is presented here:

See Also: "Setting QOS, Timeout Interval, Namespace, and Port Number"

`OCI_ATTR_CHNF_TABLENAMES` (datatype is `(OCIColl *)`) attributes provided to retrieve the list of table names that were registered. These attributes are available from the subscription handle, after the query is executed.

`OCI_ATTR_CHNF_ROWIDS` is a boolean attribute (default `FALSE`). If set to `TRUE`, then the change notification message includes row level details such as operation type and `ROWID`.

`OCI_ATTR_CHNF_OPERATIONS` - This is a `ub4` flag that can be used to selectively filter notifications based on operation type. Flags stored are:

- `OCI_OPCODE_ALL` - All operations

- `OCI_OPCODE_INSERT` - Insert operations on the table

- `OCI_OPCODE_UPDATE` - Update operations on the table

- `OCI_OPCODE_DELETE` - Delete operations on the table

`OCI_ATTR_CHNF_CHANGELAG` - This is a `ub4` value that can be used by the client to specify the number of transactions by which the client is willing to lag behind. This option can be used by the client as a throttling mechanism for change notification messages. When this option is chosen, ROWID level granularity of information will not be available in the notifications even if `OCI_ATTR_CHNF_ROWID` was set to `TRUE`

Once the `OCISubscriptionRegister()` call is invoked, none of the above attributes can be subsequently modified on the registration already created. Any attempt to modify those attributes is not reflected on the registration already created, but it does take effect on newly created registrations that use the same registration handle.

### Change Notification Descriptor

The change notification descriptor is passed into the `desc` parameter of the notification callback specified by the application. The following attributes are specific to change notification. The OCI type constant of the change notification descriptor is `OCI_DTYPE_CHDES`.

`OCI_ATTR_CHDES_DBNAME` (`OCIText *`) - Name of the database (source of the change notification)

`OCI_ATTR_CHDES_NFYTYPE` - Flags describing the notification type:

- `OCI_EVENT_NONE` - No further information about the change notification

- `OCI_EVENT_STARTUP` - Startup

- `OCI_EVENT_SHUTDOWN` - Instance shutdown

- `OCI_EVENT_SHUTDOWN_ANY` - Any instance shutdown - Real Application Clusters (RAC)

- `OCI_EVENT_DEREG` - Unregistered or timed out

- `OCI_EVENT_OBJCHANGE` - Object change notification

`OCI_ATTR_CHDES_TABLE_CHANGES` - A collection type describing operations on tables of datatype `(OCIColl *)`. Each element of the collection is a table change descriptor of type `OCI_DTYPE_TABLE_CHDES` which has the following attributes:

- `OCI_ATTR_CHDES_TABLE_NAME` `(OCIText *)` - Schema annotated table name.

- `OCI_ATTR_CHDES_TABLE_OPFLAGS` (`ub4`) - Flag field describing the operations on the table. Each of the following flag fields is in a separate bit position in the attribute:

  - `OCI_OPCODE_ALLROWS` - The table is completely invalidated.

  - `OCI_OPCODE_INSERT`- Insert operations on the table.

  - `OCI_OPCODE_UPDATE` - Update operations on the table.

  - `OCI_OPCODE_DELETE` - Delete operations on the table.

  - `OCI_OPCODE_ALTER` - Table altered (schema change) - this includes DDL statements and internal operations that cause row migration.

  - `OCI_OPCODE_DROP` - Table dropped.

- `OCI_ATTR_CHDES_TABLE_ROW_CHANGES` - This is an embedded collection describing the changes to the rows within the table. Each element of the collection is a row change descriptor of type `OCI_DTYPE_ROW_CHDES` which has the following attributes:

  - `OCI_ATTR_CHDES_ROW_ROWID` (`OraText *`) - String representation of a `ROWID`.

  - `OCI_ATTR_CHDES_ROW_OPFLAGS` - Reflects the operation type: `INSERT`, `UPDATE`, `DELETE`, or `OTHER`.

### Database Change Notification Example

The following is a simple OCI program. See the comments.

    /***************************************************************************
     The following is an example of an OCI program that creates a registration
     involving the HR.EMPLOYEES and HR.DEPARTMENTS tables and waits to
     receive a Notification. After compilation the program can be executed
     at the command prompt as
     ./regquery

      Sample output after registration:
      --------------------------------
      Allocated handles
      Created Registration
      Registered query SELECT MANAGER_ID from EMPLOYEES where EMPLOYEE_ID=206
      Registered query SELECT department_id  from DEPARTMENTS where DEPARTMENT_NAME =
       'Payroll'
      Waiting for Notification to arrive.

      Now we can update the EMPLOYEES table and receive the notification. From
      sqlplus perform the following

      UPDATE EMPLOYEES SET SALARY=SALARY WHERE EMPLOYEE_ID = 200;
      COMMIT;

      Program output from notification handler:
      -----------------------------------------
      Received Notification
      EMPLOYEE table modified
      Number of rows modified is 1
      HR.EMPLOYEES table has been modified in row AAAKCeAABAAAKC1AAC
      Executing stmt select * from HR.EMPLOYEES where rowid='AAAKCeAABAAAKC1AAC'
      First column of modified row is 200

    *******************************************************************************/

    #ifndef S_ORACLE
    # include<s.h>
    #endif

    #include <stdio.h>
    #include <stdlib.h>
    #include <string.h>
    #include <oci.h>

    #define MAXSTRLENGTH 1024

    static int notifications_processed =0;
    static void checker();

    int main(int argc, char **argv)
    {
      NotificationDriver();
    }

    void checker(errhp, status)
    OCIError *errhp;
    sword status;
    {
      text errbuf[512];
      sb4 errcode = 0;
      int retval = 1;

      switch (status)
      {
      case OCI_SUCCESS:
        retval = 0;
        break;
      case OCI_SUCCESS_WITH_INFO:
        (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
        break;
      case OCI_NEED_DATA:
        (void) printf("Error - OCI_NEED_DATA\n");
        break;
      case OCI_NO_DATA:
        (void) printf("Error - OCI_NODATA\n");
        break;
      case OCI_ERROR:
        (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                            errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
        (void) printf("Error - %.*s\n", 512, errbuf);
        break;
      case OCI_INVALID_HANDLE:
        (void) printf("Error - OCI_INVALID_HANDLE\n");
        break;
      case OCI_STILL_EXECUTING:
        (void) printf("Error - OCI_STILL_EXECUTE\n");
        break;
      case OCI_CONTINUE:
        (void) printf("Error - OCI_CONTINUE\n");
        break;
      default:
        break;
      }
    }

    void my_notification_callback(ctx, subscrhp, payload, payl, descriptor, mode)
    dvoid *ctx;
    OCISubscription *subscrhp;
    dvoid *payload;
    ub4 *payl;
    dvoid *descriptor;
    ub4 mode;
    {
      dvoid *change_descriptor =  descriptor;
      ub4   notify_type;
      OCIEnv *envhp;
      OCIError *errhp;
      OCIServer *srvhp;
      OCISvcCtx *svchp;
      OCISession *usrhp;
      OCIStmt *stmthp;
      dvoid *tmp;

      dvoid *elemind = (dvoid *)0;
      OCIColl *table_changes = (OCIColl  *)0 ;
                       /* Collection of pointers to table chg descriptors */
      dvoid **table_descp;          /* Pointer to Table Change Descriptor */
      dvoid *table_desc;              /*  Table Change Descriptor */
      ub4 num_rows = 0;
      ub4 table_op;
      ub4 num_tables = 0;
      ub2 i, j;
      boolean exist;
      text *table_name;

      printf("Received Notification\n");

      /* Initialize environment and allocate Error Handle.
         Note that the environment has to be initialized in object mode
         since we might be operating on collections.
      */
      OCIEnvCreate( (OCIEnv **) &envhp, OCI_OBJECT, (dvoid *)0,
                     (dvoid * (*)(dvoid *, size_t)) 0,
                     (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                     (void (*)(dvoid *, dvoid *)) 0,
                     (size_t) 0, (dvoid **) 0 );

      OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                       (size_t) 0, (dvoid **) 0);

      /* Get the Notification Type */
      checker(errhp,
              OCIAttrGet( change_descriptor, OCI_DTYPE_CHDES, &notify_type, NULL,
                    OCI_ATTR_CHDES_NFYTYPE, errhp));
      if (notify_type == OCI_EVENT_SHUTDOWN)
        printf("Shutdown Notification\n");
      else if (notify_type == OCI_EVENT_DEREG)
        printf("Registration Removed\n");

      if (notify_type != OCI_EVENT_OBJCHANGE)
      {
        OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
        OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR);
        return;
      }

      /* The below code is only executed if the notification is of type
         OCI_EVENT_OBJCHANGE
       */
      /* server contexts */
      OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp,
                                OCI_HTYPE_SERVER,
                       (size_t) 0, (dvoid **) 0);
      OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp,
                                 OCI_HTYPE_SVCCTX,
                                  (size_t) 0, (dvoid **) 0);

      /* Allocate a statement handle */
      OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
                      (ub4) OCI_HTYPE_STMT, 52, (dvoid **) &tmp);

      /* set attribute server context in the service context */
      OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
                  (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) errhp);

      checker(errhp, OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0,
              (ub4) OCI_DEFAULT));

       /* allocate a SESSION  handle */
      OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION,
                         (size_t) 0, (dvoid **) 0);

      OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
                 (dvoid *)((text *)"HR"),
                 (ub4)strlen((char *)"HR"),  OCI_ATTR_USERNAME, errhp);

      OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
                 (dvoid *)((text *)"HR"), (ub4)strlen((char *)"HR"),
                 OCI_ATTR_PASSWORD, errhp);

      checker(errhp,OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
                                     OCI_DEFAULT));
      OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
                 (dvoid *)usrhp, (ub4)0, OCI_ATTR_SESSION, errhp);

      /* Obtain the collection of table change descriptors */
      checker(errhp,OCIAttrGet(change_descriptor, OCI_DTYPE_CHDES, &table_changes,
                               NULL, OCI_ATTR_CHDES_TABLE_CHANGES, errhp));
     /* Obtain the size of the collection (i.e number of tables modified) */
      if (table_changes)
        checker(errhp,OCICollSize(envhp, errhp, (CONST OCIColl *) table_changes,
                &num_tables));
       else
         num_tables =0;

      /* For each element of the collection, extract the table name of the modified
         table */
      for (i=0; i < num_tables; i++)
      {
        OCIColl *row_changes = (OCIColl  *)0;
        /* Collection of pointers to row chg. Descriptors */
        dvoid **row_descp;            /*  Pointer to Row Change Descriptor */
        dvoid   *row_desc;               /*   Row Change Descriptor */
        text *row_id;
        ub4 rowid_size;
        text *ocistmt;
        OCIDefine *defnp1 = (OCIDefine *)0;
        char *outstr;

        checker(errhp,OCICollGetElem(envhp, errhp, (OCIColl *) table_changes, i,
                                     &exist, &table_descp, &elemind));

        table_desc = *table_descp;
        checker(errhp,OCIAttrGet(table_desc, OCI_DTYPE_TABLE_CHDES, &table_name,
                                 NULL,
                                 OCI_ATTR_CHDES_TABLE_NAME, errhp));
        if (strcmp(table_name, "HR.EMPLOYEES") == 0)
          printf("EMPLOYEE table modified \n");
        else if (strcmp(table_name, "HR.DEPARTMENTS") == 0)
          printf("DEPARTMENTS table modified \n");

        checker(errhp,OCIAttrGet (table_desc, OCI_DTYPE_TABLE_CHDES,
                                  (dvoid *)&table_op, NULL,
                                   OCI_ATTR_CHDES_TABLE_OPFLAGS, errhp));

        /* If the ROWID granularity of info not available, move-on. Rowids
           can be rolled up into a full table notification if too many rows
           were updated on a single table or insufficient shared memory on
           the server side to hold rowids
         */
        if (table_op & OCI_OPCODE_ALLROWS)
        {
           printf("Full Table Invalidation\n");
           continue;
         }

         /* Obtain the collection of  ROW CHANGE descriptors */
        checker(errhp,OCIAttrGet (table_desc, OCI_DTYPE_TABLE_CHDES, &row_changes,
                                   NULL, OCI_ATTR_CHDES_TABLE_ROW_CHANGES, errhp));

        if (row_changes)
          checker(errhp,OCICollSize(envhp, errhp, row_changes, &num_rows));
        else
          num_rows =0;

        printf ("Number of rows modified is %d\n", num_rows);
        fflush(stdout);
        for (j=0; j<num_rows; j++)
        {
          OCICollGetElem(envhp, errhp, (OCIColl *) row_changes,
                         j, &exist, &row_descp, &elemind);
          row_desc = *row_descp;

          OCIAttrGet (row_desc, OCI_DTYPE_ROW_CHDES, (dvoid *)&row_id,
                      &rowid_size, OCI_ATTR_CHDES_ROW_ROWID, errhp);
          printf ("%s table has been modified in row %s \n", table_name, row_id);
          fflush(stdout);
          ocistmt = (text *)malloc(MAXSTRLENGTH*sizeof(char));

          /* QUERY FROM DATABASE TO VIEW CONTENTS OF CHANGED ROW */
          sprintf (ocistmt, "select * from %s where rowid='%s'", table_name, row_id);
          printf("Executing stmt %s\n", ocistmt);

          /* prepare query statement*/
          checker(errhp,OCIStmtPrepare(stmthp, errhp, ocistmt,
                                       (ub4)strlen((char *)ocistmt),
                                       (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
          outstr = (char *)malloc(MAXSTRLENGTH*sizeof(char));

          checker(errhp,OCIDefineByPos(stmthp, &defnp1, errhp, 1, (dvoid *)outstr,
                                       MAXSTRLENGTH * sizeof(char),
                                       SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0,
                                       OCI_DEFAULT));

          /* execute the statement */
          checker(errhp,OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4) 0,
                                       (CONST OCISnapshot *) NULL,
                                       (OCISnapshot *) NULL, OCI_DEFAULT));

          printf("First column of modified row is %s\n", outstr);
        }  /* Loop for j in 1..numrows */

      } /* Loop for I in 1..numtables */

      /* End session and detach from server */
      checker(errhp, OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT));
      checker(errhp, OCIServerDetach(srvhp, errhp, OCI_DEFAULT));
       if (stmthp)
        OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
      if (errhp)
        OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR);
      if (srvhp)
        OCIHandleFree((dvoid *)srvhp, OCI_HTYPE_SERVER);
      if (svchp)
        OCIHandleFree((dvoid *)svchp, OCI_HTYPE_SVCCTX);
      if (usrhp)
        OCIHandleFree((dvoid *)usrhp, OCI_HTYPE_SESSION);
      if (envhp)
        OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);

      notifications_processed++;
    }  /* End function my_notification_callback */

    /* The following routine creates registrations and waits for notifications. */
    void NotificationDriver()
    {
      OCISvcCtx *svchp;
      OCIError *errhp;
      OCISession *usrhp;
      OCIStmt *stmthp;
      OCIEnv *envhp;
      OCIServer *srvhp;
      text *username;
      OCISubscription *subscrhp;
      ub4 namespace = OCI_SUBSCR_NAMESPACE_DBCHANGE;
      ub4 timeout = 1800;
      text dname[MAXSTRLENGTH];
      OCIDefine *defnp1 = (OCIDefine *)0;
      OCIDefine *defnp2 = (OCIDefine *)0;
      OCIDefine *defnp3 = (OCIDefine *)0;
      OCIDefine *defnp4 = (OCIDefine *)0;
      OCIDefine *defnp5 = (OCIDefine *)0;
      int mgr_id =0;
      int dept_id =0;
      dvoid *tmp;
      boolean rowids_needed = TRUE;

      char query_text1[] = "SELECT MANAGER_ID from EMPLOYEES where EMPLOYEE_ID=206";
      char query_text2[] =
      "SELECT department_id  from DEPARTMENTS where DEPARTMENT_NAME = 'Payroll'";
      /* DEPARTMENTS  is also a cached object */

      /* Initialize the environment. The environment has to be initialized
         with OCI_EVENTS and OCI_OBJECTS to create a change notification
         registration and receive notifications.
      */
      OCIEnvCreate( (OCIEnv **) &envhp, OCI_EVENTS|OCI_OBJECT, (dvoid *)0,
                        (dvoid * (*)(dvoid *, size_t)) 0,
                        (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                        (void (*)(dvoid *, dvoid *)) 0,
                        (size_t) 0, (dvoid **) 0 );

      OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                             (size_t) 0, (dvoid **) 0);
       /* server contexts */
      OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                            (size_t) 0,
                            (dvoid **) 0);
      OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                             (size_t) 0, (dvoid **) 0);
       checker(errhp,OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0,
               (ub4) OCI_DEFAULT));
       OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX,
                       52, (dvoid **)0);
      /* set attribute server context in the service context */
      OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
                  (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) errhp);

       /* allocate a user context handle */
      OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION,
                                   (size_t) 0, (dvoid **) 0);

      OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
                 (dvoid *)((text *)"HR"), (ub4)strlen((char *)"HR"),
                  OCI_ATTR_USERNAME, errhp);

     OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
                (dvoid *)((text *)"HR"), (ub4)strlen((char *)"HR"),
                 OCI_ATTR_PASSWORD, errhp);
       checker(errhp,OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
               OCI_DEFAULT));
       /* Allocate a statement handle */
      OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
                                    (ub4) OCI_HTYPE_STMT, 52, (dvoid **) &tmp);

      OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)usrhp, (ub4)0,
                           OCI_ATTR_SESSION, errhp);

      /* allocate subscription handle */
      OCIHandleAlloc ((dvoid *) envhp, (dvoid **) &subscrhp, OCI_HTYPE_SUBSCRIPTION,
                       (size_t) 0,
                        (dvoid **) 0);
      printf("Allocated handles\n");

      /* set the namespace to DBCHANGE */
      OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,  (dvoid *) &namespace,
                  sizeof(ub4),
                  OCI_ATTR_SUBSCR_NAMESPACE, errhp);
      /* Associate a notification callback */
      OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,
                  (void *)my_notification_callback,  0,
                  OCI_ATTR_SUBSCR_CALLBACK, errhp);

      /* Allow extraction of rowid information */
      checker(errhp, OCIAttrSet (subscrhp, OCI_HTYPE_SUBSCRIPTION,
                      (dvoid *)&rowids_needed, sizeof(ub4),
                      OCI_ATTR_CHNF_ROWIDS, errhp));

      /* Can optionally provide a client specific context using
         OCI_ATTR_SUBSCR_CTX */

      /* Set a timeout value of half an hour */
      OCIAttrSet(subscrhp, OCI_HTYPE_SUBSCRIPTION,
                 (dvoid *)&timeout, 0, OCI_ATTR_SUBSCR_TIMEOUT, errhp);

      /* Create a new registration in the  DBCHANGE namespace */
      checker(errhp,OCISubscriptionRegister(svchp, &subscrhp, 1, errhp, OCI_DEFAULT));

      printf("Created Registration\n");
    /* Multiple queries can now be associated with the subscription */

      /* Prepare the statement */
      checker(errhp,OCIStmtPrepare (stmthp, errhp, query_text1,
                                    strlen(query_text1), OCI_V7_SYNTAX, OCI_DEFAULT));
      checker(errhp,OCIDefineByPos(stmthp, &defnp1, errhp, 1, (dvoid *)&mgr_id,
                                   sizeof(mgr_id), SQLT_INT, (dvoid *)0,
                                   (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
      /* Associate the statement with the subscription handle */
      checker(errhp,OCIAttrSet (stmthp, OCI_HTYPE_STMT, subscrhp, 0,
                                OCI_ATTR_CHNF_REGHANDLE, errhp));

      /* Execute the statement The execution of the statement  performs the object
     registration */
      checker(errhp,OCIStmtExecute (svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                     (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL ,
                     OCI_DEFAULT));
      printf("Registered query %s\n", query_text1);

      /* Use the same registration for the departments table */
      checker(errhp,OCIStmtPrepare (stmthp, errhp, query_text2,
                    strlen(query_text2), OCI_V7_SYNTAX, OCI_DEFAULT));

      checker(errhp,OCIDefineByPos(stmthp, &defnp3,
                   errhp, 1, (dvoid *)&dept_id, sizeof(dept_id),
                    SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
      checker(errhp,OCIAttrSet (stmthp, OCI_HTYPE_STMT, subscrhp, 0,
                         OCI_ATTR_CHNF_REGHANDLE, errhp));
      checker(errhp,OCIStmtExecute (svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                     (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL ,
                     OCI_DEFAULT));
      printf("Registered query %s\n", query_text2);

      printf("Waiting for Notifications to arrive\n");
      /* Wait for notifications to arrive */
      while (notifications_processed != 1);

      /* Unregister the subscription */
      checker(errhp,
               OCISubscriptionUnRegister(svchp,subscrhp, errhp, OCI_DEFAULT));

      /* End the session and detach from the server */
      checker(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4) 0));
      checker(errhp, OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT));

      /* Free all the handles */
      OCIHandleFree((dvoid *)subscrhp, OCI_HTYPE_SUBSCRIPTION);
      OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
      OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
      OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
      OCIHandleFree((dvoid *) usrhp, (ub4) OCI_HTYPE_SESSION);
      OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);
      OCIHandleFree((dvoid *) envhp, (ub4) OCI_HTYPE_ENV);
    }

## Database Startup and Shutdown

The OCI functions, `OCIDBStartup()` and `OCIDBShutdown()`, provide the minimal interface needed to start up and shut down an Oracle database. Before calling `OCIDBStartup()`, the C program must connect to the server and start a `SYSDBA` or `SYSOPER` session in the preliminary authentication mode. This mode is the only one permitted when the instance is not up and it is used only to bring up the instance. A call to `OCIDBStartup()` will start up one server instance without mounting or opening the database. To mount and open the database, end the preliminary authentication session and start a regular `SYSDBA` or `SYSOPER` session to execute the appropriate `ALTER` `DATABASE` statements.

An active `SYSDBA` or `SYSOPER` session is needed to shut down the database. For all modes other than `OCI_DBSHUTDOWN_ABORT`, make two calls to `OCIDBShutdown()`: one to initiate shutdown by prohibiting further connections to the database, followed by the appropriate `ALTER` `DATABASE` commands to dismount and close it; and the other call to finish shutdown by bringing the instance down. In special circumstances, to shut down the database as fast as possible, just call `OCIDBShutdown()` in the `OCI_DBSHUTDOWN_ABORT` mode, which is equivalent to `SHUTDOWN` `ABORT` in SQL*Plus.

Both of these functions require a dedicated connection to the server. `ORA-106` will be signaled if an attempt is made to start up or shut down the database when connected to a shared server through a dispatcher.

The `OCIAdmin` administration handle datatype is used to make the interface extensible. `OCIAdmin` is associated with the handle type `OCI_HTYPE_ADMIN`. Passing a value for the `OCIAdmin` parameter, `admhp`, is optional for `OCIDBStartup()` and is not needed by `OCIDBShutdown()`.

See Also: "OCIDBStartup()" "OCIDBShutdown()" "Administration Handle Attributes" Oracle Database Administrator's Guide

### Examples of Startup and Shutdown in OCI

To do a startup, you must be connected to the database as `SYSOPER` or `SYSDBA` in `OCI_PRELIM_AUTH` mode. You cannot be connected to a shared server through a dispatcher. To use a client-side parameter file (`pfile`), the attribute `OCI_ATTR_ADMIN_PFILE` must be set in the administration handle using `OCIAttrSet()`; otherwise, a server-side parameter file (`spfile`) will be used. In the latter case, pass `(OCIAdmin *)0`. A call to `OCIDBStartup()` starts up one instance on the server.

The following sample code uses a client-side parameter file (`pfile`) that is set in the administration handle:

    ...

    /*  Example 0 - Startup:  */
    OCIAdmin *admhp;
    text *mount_stmt = (text *)"ALTER DATABASE MOUNT";
    text *open_stmt = (text *)"ALTER DATABASE OPEN";
    text *pfile = (text *)"/ade/viewname/oracle/work/t_init1.ora";

    /* Start the authentication session */
    checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp,
             OCI_CRED_RDBMS, OCI_SYSDBA|OCI_PRELIM_AUTH));

    /* Allocate admin handle for OCIDBStartup */
    checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &admhp,
             (ub4) OCI_HTYPE_ADMIN, (size_t) 0, (dvoid **) 0));

    /* Set attribute pfile in the admin handle
    (do not do this if you want to use the spfile) */
    checkerr (errhp, OCIAttrSet( (dvoid *) admhp, (ub4) OCI_HTYPE_ADMIN,
              (dvoid *) pfile, (ub4) strlen(pfile),
              (ub4) OCI_ATTR_ADMIN_PFILE, (OCIError *) errhp));

    /* Startup in NOMOUNT mode */
      checkerr(errhp, OCIDBStartup(svchp, errhp, admhp, OCI_DEFAULT, 0));
       checkerr(errhp, OCIHandleFree((dvoid *) admhp, (ub4) OCI_HTYPE_ADMIN));

    /* End the authentication session */
    OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);

    /* Start the sysdba session */
    checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
             OCI_SYSDBA));

    /* Mount the database */
    checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, mount_stmt, (ub4)
             strlen((char*) mount_stmt),
             (CONST OraText *) 0, (ub4) 0, (ub4) OCI_NTV_SYNTAX, (ub4)
             OCI_DEFAULT));
    checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
             (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
    checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

    /* Open the database */
    checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, open_stmt, (ub4)
             strlen((char*) open_stmt),
             (CONST OraText *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
    checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
             (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
    checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

    /* End the sysdba session */
    OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);
    ...

To do a shutdown, you must be connected to the database as `SYSOPER`, or `SYSDBA`. You cannot be connected to a shared server through a dispatcher. When shutting down in any mode other than `OCI_DBSHUTDOWN_ABORT`, the following procedure should be followed:

1. Call `OCIDBShutdown()` in `OCI_DEFAULT`, `OCI_DBSHUTDOWN_TRANSACTIONAL`, `OCI_DBSHUTDOWN_TRANSACTIONAL_LOCAL`, or `OCI_DBSHUTDOWN_IMMEDIATE` mode to prohibit further connects.

2. Use the necessary `ALTER` `DATABASE` commands to close and dismount the database.

3. Call `OCIDBShutdown()` in `OCI_DBSHUTDOWN_FINAL` mode to shut down the instance.

    /*  Example 1 - Orderly shutdown:  */
    ...
    text *close_stmt = (text *)"ALTER DATABASE CLOSE NORMAL";
    text *dismount_stmt = (text *)"ALTER DATABASE DISMOUNT";

    /* Start the sysdba session */
    checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
             OCI_SYSDBA));

    /* Shutdown in the default mode (transactional, transactional-local,
      immediate would be fine too) */
    checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0, OCI_DEFAULT));

    /* Close the database */
    checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, close_stmt, (ub4)
             strlen((char*) close_stmt),
             (CONST OraText *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX,
             (ub4) OCI_DEFAULT));
    checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
            (OCISnapshot *) NULL,
            (OCISnapshot *) NULL, OCI_DEFAULT));
    checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

    /* Dismount the database */
    checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, dismount_stmt,
             (ub4) strlen((char*) dismount_stmt), (CONST OraText *)0, (ub4)0,
             (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
    checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
             (OCISnapshot *) NULL,
             (OCISnapshot *) NULL, OCI_DEFAULT));
    checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

    /* Final shutdown */
    checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0,
             OCI_DBSHUTDOWN_FINAL));

    /* End the sysdba session */
    checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
    ...

The next shutdown example uses `OCI_DBSHUTDOWN_ABORT` mode:

    /*  Example 2 - Shutdown using abort:  */
    ...
    /* Start the sysdba session */
    ...
    checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
             OCI_SYSDBA));

    /* Shutdown in the abort mode */
    checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0,
             OCI_SHUTDOWN_ABORT));

    /* End the sysdba session */
    checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
    ...